texts:
I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every 10MB and the standby logs are rotated every day.
My parameters are as follows:

postgres=# select name,setting,unit from pg_settings where name like '%log%';
            name             |            setting             | unit
-----------------------------+--------------------------------+------
 log_autovacuum_min_duration | -1                             | ms
 log_checkpoints             | on                             |
 log_connections             | on                             |
 log_destination             | stderr                         |
 log_directory               | /apps/postgres/pg_log          |
 log_disconnections          | on                             |
 log_duration                | off                            |
 log_error_verbosity         | default                        |
 log_executor_stats          | off                            |
 log_file_mode               | 0600                           |
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log |
 log_hostname                | on                             |
 log_line_prefix             | %u %h %p                       |
 log_lock_waits              | on                             |
 log_min_duration_statement  | 10000                          | ms
 log_min_error_statement     | error                          |
 log_min_messages            | warning                        |
 log_parser_stats            | off                            |
 log_planner_stats           | off                            |
 log_rotation_age            | 1440                           | min
 log_rotation_size           | 10240                          | kB
 log_statement               | ddl                            |
 log_statement_stats         | off                            |
 log_temp_files              | -1                             | kB
 log_timezone                | Australia/NSW                  |
 log_truncate_on_rotation    | off                            |
 logging_collector           | on                             |
 syslog_facility             | local0                         |
 syslog_ident                | postgres                       |

My problem is that the logfiles in the production database aren't being rotated at all, while the standby logs are being rotated as expected every day. This is a high availability application so I'm keeping it running at present by copying the logfile to a new file then echoing "" to the current logfile.
Is there anyway I can fix this problem without an outage to the system?


texts: Can two servers access the same database files?

texts:
I'm designing an application and considering some options regarding the database system since I'm not familiar with Microsoft SQL Server. I would like to know if it's possible to have 2 servers sharing the same files, as shown below.

The idea is that the data is in a raid system so it would grant us some safety. This way we would save effort in synchronizing both databases and save some money on storage. We only need one database at a time, the secondary is just for use in case of failure of the first.

Is this possible?

I'm open to different approaches. The main problem is the database redundancy, our application must guarantee that.

At the moment I'm using Windows 7 and SQL Server 2008R2.


texts: Synchronizing data from AWS Sql Server to local database

texts: I have a situation when I need to synchronize some tables from Sql Server database to my local Sql Server database and vice versa. What are my options? I can not find any useful information about replications. Some says it is not supported on AWS. May be mirroring or any other way?


texts: How will Oracle fill a datafile added to a tablespace?

texts:
I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace -

will the first datafile continue to be used until it reaches full capacity and only then will the second one start to be used for data or
the first datafile will forever remain only 50% used as the data changes are written to the new datafile or
something else happens which I haven't thought of?

I need this info to plan a strategy for dealing with increasing the size of tablespaces without wasting physical space.


texts: Run subquery once / optimize join with subquery without needing to use temporary table

texts:
Until today I thought I had the hang of optimizing queries with joins on subqueries.  But I've been trying to optimize a query and on a whim I tried an old method that I thought I didn't need to use anymore - I used temporary tables.

Slow query (takes 5 minutes)...

select (columns) from big_transactions_table t inner join

  (select (columns) from small_info_table where (conditions)) q1

on q1.key = t.key
group by (columns)

Fast query using a temporary table...

select (columns) from small_info_table into #q1 where (conditions)

go

select (columns) from big_transactions_table t inner join #q1
on #q1.key = t.key
group by (columns)

Took 14 seconds!

I was under the impression that when you have a subquery in a join like this the SQL engine would get the data first before joining it with the outer query.  Now I'm not so sure.   Can anyone tell me why there is a big difference in execution time, and if there's a simple way to speed up the query without using a temporary table?


texts: Problems Exporting Long Columns to Flat File Using SSIS

texts:
We have an SSIS package that is going to generate files to be consumed by Google Big Query.  The files will be gzipped .tsv files.

One of the requirements is that the file be UTF-8.  We have set this in the flat file destination so that it's 65001 - UTF-8.  After this, the generated gzip files are consumed to Big Query correctly.

The problem now is that some fields have character lengths of up to 21,000 characters.  The DT_WSTR doesn't allow for this size.

Changing the flat file destination field to DT_NTEXT yields the following error message

  Error: 0xC020802E at 14_3 Data Flow into MyTSV, Flat File Destination MyDestination [12]: The data type for "Flat File Destination MyDestination.Inputs[Flat File Destination Input].Columns[Value]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

All solutions I have read involve converting back to DT_WSTR or changing the code page back to 1252 which is not an option due to the Big Query code page requirement and the length of the data.  Is there another solution for this problem?


texts:
I have access to an enterprise level DB used throughout the organization with tight constraints on who can access things. It contains almost all our data. I am a user of that DB with only read rights. I also have admin rights to my 'team's server' where I can create DBs for projects, small data storage etc. I can create Server Agent jobs etc here in addition to new tables. Both are SQL(but not sure on edition, I am confident both are 2008 or later, with the local copy probably at least 2012)

What I need to do is use a T-SQL query I have built to pull data from the main server and run it every so often (week or two) and store it in smaller DB on a different server. This is so I can grant access rights to another user who can't access the main DB (the data is needed for a third party application). (It is all legit with the managers, there are just security issues with giving them rights to the full data.)

Can I do that, if so how? I thought a SQL Server Agent Job would work, but I don't know how to set that up. In the Steps tab I can insert a query, but the list only populates with the DBs I have admin rights to. I need to run the query on the org level DB and then have it save the results into a new DB I have just created. Does the job have to be setup in the higher level DB, or can it be setup in the lower one and just ping the other? I thought of maybe adding a line to the SQL telling it which DB to ping, but I don't know how to handle telling it to use Windows authentication for the server password.

Sorry, I don't know which question is the right one, so I put a few showing my line of thinking.


texts: Visual Studio 2010 Shell Error - is it Causing this SQL Server error?

texts:
Visual Studio 2010 Isolated Shell has the infamous "Invalid License Data. Reinstall" error. I have SSMS 2014. I have tried reinstalling several times, I installed Visual Studio 2013 but SSMS doesn't recognize it. When I attempt to open SSMS, it says something like components missing, reinstall. So when I repair it installs the same VS 2010 with same issue.

We have an application with a SQL Server backend database. We have a vendor catalog and then import items to our item database. However, it fails and gives this error code below. So my question is: Could this VS 2010 error be causing this error or is this likely an unrelated issue?

  mscorlib 06/18/2016 8:20:42 PM LoadSettings Object reference not
  set to an instance of an object.
  at PosBeData.Settings.LoadSettings() PosBeData 06/18/2016 8:20:43 PM Time

  Update To: 06/19/2016 12:20 AM 06/18/2016 8:32:47 PM frmImportAdd
  String or binary data would be truncated. The INSERT statement
  conflicted with the FOREIGN KEY constraint "FK_UPC_Item". The conflict
  occurred in database "PosBe", table "dbo.Item", column 'Item_ID'. The
  INSERT statement conflicted with the FOREIGN KEY constraint
  "FK_VendorPartNo_Item". The conflict occurred in database "PosBe",
  table "dbo.Item", column 'Item_ID'. The statement has been terminated.
  The statement has been terminated. The statement has been terminated.

  Server stack trace:     at
  System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message
  reply, MessageFault fault, String action, MessageVersion version,
  FaultConverter faultConverter)    at
  System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime
  operation, ProxyRpc& rpc)    at
  System.ServiceModel.Channels.ServiceChannel.Call(String action,
  Boolean oneway, ProxyOperationRuntime operation, Object[] ins,
  Object[] outs, TimeSpan timeout)    at
  System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage
  methodCall, ProxyOperationRuntime operation)    at
  System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage
  message)

  Exception rethrown at [0]:     at
  System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
  reqMsg, IMessage retMsg)    at
  System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
  msgData, Int32 type)    at
  PosBeData.PosBeService.GetAddCatalogItems(dtoV_VendorCatalog[]
  VendorCatalogItems, String Import_CD, Int32 Category_ID, Int32
  Sub_Category_ID, Boolean CreateInventory, Boolean CreateCase, String
  Default_Mfg, UserAuth ua)    at
  PosBeData.V_VendorCatalogCollection.AddSelectedItems(String Import_CD,
  Int32 Category_ID, Int32 Sub_Category_ID, Boolean CreateInventory,
  Boolean CreateCase, String Default_Mfg)    at
  PosBe.frmVendorCatalog.AddItemsThread() mscorlib


texts:
I have a Postgresql table that has contents of a column either in English or Spanish Like so:

202707  Redness or swelling on the cheek.
202575  Preguntas acerca de medicamentos antivirales de la gripe.
202433  Sounds very sick or weak to the triager.
201505  El paciente quiere ser visto.

I would like to query such that I can get only the English rows or only the Spanish rows.

I have read a bit about PostgreSQL dictionaries, to_tsvector and to_tsquery and I feel like there should be a easy way to identify a string as a English/Spanish.


texts: Where is located sp_msreplcheck_publish stored procedure?

texts:
sp_msreplcheck_publish is a sp that checks if you have sufficient rights to run a statement to alter a published table (transactional replication).

Trying to alter a published table without elevated rights fires trigger tr_MStran_altertable that calls sp_MStran_ddlrepl that execute sp_msreplcheck_publish. It returns correctly :

  Only members of the sysadmin fixed server role or db_owner fixed
  database role can perform this operation.

The mystery for me  is that I cannot find this sp in published database or in master. Is it generated on demand?


texts:
As part of a security policy, the company I work for has given me the task of changing the sa's and SQL Server Agent service accounts' passwords for several instances.

What's the possible impact of doing this? Is there anything in particular I should watch out for? Is there any whitepaper, guide or even blog post about this kind of task? What kind of 'backup' plan should I have in place in case anything goes wrong?

Any guidance is appreciated.


texts: Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records


texts: Getting new and updated data with an archive column

texts:
I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers where SQL Server 2008 Standard Edition is installed. CDC, therefore cannot be used as it is an Enterprise feature. The plan is this:

Archive column, smallint - 0=new (default constraint), 1=archived, 2=updated (trigger)
Default constraint Archive=0
Update trigger Archive=2
Index on archive, timestamp

The SSIS package will then get all records where Archive=0, insert those into the destination then get all records where Archive=2 and update them in the destination.

What I need help with is the setting of the Archive column to 1 after the SSIS procedure is done writing new or updated rows. What would be the most efficient way of setting all of the rows processed without touching any rows that might have been inserted or updated after the initial select?


texts: Find max date in multiple columns in SSIS

texts:
I have csv file source and it have 5 date columns . I need to create a new column which will have the latest date after comparing  all the 5 columns .

Expample :

source
![cust id  dt1 dt2 dt3 dt4 dt5
101]1


texts:
I have this table:

I want to make a query that allows me to show the last value in fecha_gestion
grouped by NitSec. It would be something like this:

SELECT id_log, fecha_gestion, nitsec
FROM table
GROUP BY nitsec
ORDER BY fecha_gestion DESC

The result I want is this:

ID_log | fecha_gestion    | nitsec
6      | 2020-01-03 09:52 | 5443
11     | 2020-01-03 10:12 | 1
15     | 2020-01-03 10:23 | 3286

After doing this I need an inner join (but I can do it easily). Thanks in advance.


texts: Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

texts:
I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables.

1, 2, 3, 4

In testing this approach, I've discovered something that doesn't make a whole lot of sense to me. When I filter on "partitioning column" on the fact view, the optimizer only seeks on the relevant tables. Additionally, if I filter on that column on the dimension table, the optimizer eliminates unnecessary tables.

However, if I filter on some other aspect of the dimension the optimizer seeks on the PK/CI of each base table.

Here are the queries in question:

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where o.ObservationDateKey >= 20000101
    and o.ObservationDateKey <= 20051231
group by od.[Year];

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.DateKey >= 20000101
    and od.DateKey <= 20051231
group by od.[Year];

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.[Year] >= 2000 and od.[Year] < 2006
group by od.[Year];

Here's a link to the SQL Sentry Plan Explorer session.

I'm working on actually partitioning the larger table to see if I get partition elimination to respond in a similar fashion.

I do get partition elimination for the (simple) query that filters on an aspect of the dimension.

In the meantime, here's a stats-only copy of the database:

https://gist.github.com/swasheck/9a22bf8a580995d3b2aa

The "old" cardinality estimator gets a less expensive plan, but that's because of the lower cardinality estimates on each of the (unnecessary) index seeks.

I'd like to know if there's a way to get the optimizer to use the key column when filtering by another aspect of the dimension so that it can eliminate seeks on irrelevant tables.

SQL Server Version:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts:
We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2.

Only recently I noticed my data returned is sorted differently.

Instead of having small letters, capital letters and numbers... (on Oracle 11r2)
I get my data sorted the opposite way:
numbers, capital letters, small letters (Oracle 12c)

This is realy confusing the end-user of various applications querying the database. What settings do I need to check/compare to fix this problem in Oracle 12c?

The queries I refer to have an order by clause.
EDIT
The settings on for these parameters are the same on both evironments:

NLS_SORT
type: string
value:

nls_language
type: string
value: DUTCH

So there is no value for NLS_SORT, what is the default then?

@phil

select * from NLS_INSTANCE_PARAMETERS;

Parameter                     Oracle11r2          Oracle12c
NLS_LANGUAGE                       DUTCH              DUTCH
NLS_TERRITORY            THE NETHERLANDS    THE NETHERLANDS
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                          BINARY           BINARY
NLS_LENGTH_SEMANTICS                BYTE             CHAR
NLS_NCHAR_CONV_EXCP                FALSE            FALSE

select * from NLS_SESSION_PARAMETERS;

these are the same as my NLS settings in Oracle SQL developer, so not very relevant I guess...

select * from NLS_DATABASE_PARAMETERS;

These are the same, only NLS_RDBMS_VERSION differs

So only NLS_LENGTH_SEMANTICS differs. Is there a way to check the values for other sessions?


texts: Find max content length of column of particular table from entire SQL server

texts:
I have a 3 SQL server: Server1,Server2 and Server3.

Each search has approx 50-60 databases.

In a particular database there is a table name table1 and it has a column named column1 with datatype varchar(max).

Now I want to change it's datatype to Nvarchar(500) but I can't as I dont know how much data consist in that column, If I blindly update it's datatype then it may happens that some of the data may be lossed.

So what I want is,I want to prepare a query to server level which gives me max content length of that column from entire server So I can get an idea about what is the max content length of that column.

So Based on that I can get max length and according to that max length,I can planned to alter that column.

I will be very grateful to you guys if someone can help me to prepare this query.


texts: Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres

texts:
My question has two parts:

. Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (depending on what is specified) but only for a single 'period'. That is, if 'year' is specified, summary stats will be calculated for whole years 2019, 2018, 2017, etc. however back the data goes. If 'month' then summary stats will be only for a given month, like Oct-2019, Sep-2019, Aug-2019, etc... But how to calculate eg. single values for 3 months Aug-2019 to Oct-2019, then Jul-2019 to Sep-2019, etc.?

. Secondly, what would be a generic version of the code to be able to use any time interval + number of periods, like '4 weeks', '6 months', '12 months', '2 years'?

SELECT
  date_trunc('year', t.time2), -- or hour, day, week, month, year
  count(1),
  percentile_cont(0.25) within group (order by t.price) as Q1,
  percentile_cont(0.5) within group (order by t.price) as Q2,
  percentile_cont(0.75) within group (order by t.price) as Q3,
  avg(t.price) as A,
  min(t.price) as Mi,
  max(t.price) as Mx

FROM my_table AS t
GROUP BY 1
ORDER BY date_trunc

Data table consists of a list of individual transactions (date -> time2 as timestamp; and price as bigint).


texts: Compilation error on stored procedure using PL/SQL in Oracle?

texts:
create or replace procedure updateStudentResult(id int,
m1 in int,
m2 in int,
m3 in int,
tot out int,
avg out float,
grd out char)
as

begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
avg:=(m1+m2+m3)/3;

if avg >= 85 then
grd:='A';

elsif (avg >= 65) then
grd:='B';

elsif (avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=avg,
grade=grd
where sid=id;

end;
/

  PLS-00103: encountered the symbol '=' when expecting one of the following: (

  these are the line where the error occurs

  if avg >= 85 then grd:='A';

  elsif (avg >= 65) then grd:='B';

  elsif (avg >= 50) then grd:='C';


texts: How do I create an automated SQL Server restore script for multiple backups?

texts:
I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script written, and I know it's not completely correct, that is why I'm here to ask for any pointers is much appreciated!

use master;
go

set nocount on

-- 1 - variable declaration
declare @dbname nvarchar(max) --database to restore
declare @backuppath nvarchar(max) --backup location
declare @filelisttable --list the table to populate
declare @sql nvarchar(max) --dynamic sql
declare @mdf nvarchar(max) --logical primary data file
declare @ndf nvarchar(max) --logical 2nd data file
declare @ldf nvarchar(max) --logical log data file
declare @dp nvarchar(200) --data file path
declare @lp nvarchar(200) --log file path
declare @backupfile nvarchar(500)

-- 2 - initialize variables
set @dbname = 'test'
set @backuppath = '\\mybackuppath\c$\test'

-- 3 - get list of files
insert into @filelisttable exec('restore filelistonly from disk = ''' +@backuppath+'''') --temptable to retrieve all back up files

-- 4 - retrieve top 6 recent data extracts
select  @backuppath  = max(backupfile)
from @filelist
where backupfile like '%.bak' --how to pick up most recent 6 back up files w/timestamp on trails?

set @sql = ('use [master])
    restore database @dbname ['+@dbname+']
        from disk = N'''+@backuppath+'''
            with replace,
            move N'''+@mdf+''' to N'''+@mdf+'''
            move N'''+@ndf+''' to N'''+@ndf+'''
            move N'''+@ldf+''' to N'''+@ldf+''''

-- 5- the 6 backup files that need to be restored daily

/** I have to run this manually everyday, and I want to automate this.
RESTORE DATABASE test FROM
    DISK = 'C:\test\test_Customer_20160518_1.bak',
    DISK = 'C:\test\test_Customer_20160518_2.bak',
    DISK = 'C:\test\test_Customer_20160518_3.bak',
    DISK = 'C:\test\test_Customer_20160518_4.bak',
    DISK = 'C:\test\test_Customer_20160518_5.bak',
    DISK = 'C:\test\test_Customer_20160518_6.bak'
    WITH REPLACE,
    MOVE 'test_Data1' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER.mdf',
    MOVE 'test_Data2' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER_1.ndf',
    MOVE 'test_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_CUSTOMER_2.ldf'
    GO
**/

use master
alter database test set multi_user with rollback immediate;

go

return

The goal of this script is to automate a restore with the variables declared, restore the 'test' database from the backup path where 6 files sit, create a temp filelist table to be used for restore, pick it up from there, and restore the test database everyday based on the latest date stamped files.

Currently, there are old and new files in there. I need a WHERE statement to only use current ones.


texts: What is the maximum latency / distance mysql replication can tolerate without having errors or wrong data?

texts:
I have two machines in a master - slave replication. I want to know if they are in two seperate places e.g. London, England and Tokyo, Japan are there going to be any latency issues.
What exactly is the maximum latency (if possible measured using distance) that mysql replication can tolerate before an error happens or before the two databases are different enough that the second one can give wrong data or any other error?


texts: SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator

texts:
I've created the following simple logon trigger on my SQL Server 2005:

CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER
FOR LOGON
AS
BEGIN
  PRINT 'Hello World'
END
GO

Which seems to be working fine in most cases except whenever the DTC is involved. Error messages I'm getting are:

The server failed to resume the transaction. Desc:4d00000273.

and:

Process ID 77 attempted to unlock a resource it does not own: DATABASE: 36 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

I've come across this article which goes some way to explaining the possible cause but the solution at the end isn't quite what I'm after.

Ultimately I'd like to use the trigger to restrict access from certain IP addresses to certain logins.


texts: Mysql replication is enabled but stuck at "waiting for binlog to be updated"

texts:
I have set up replication between an existing database (server-id=23), and a new one (server-id=51).

I have followed the steps in this stackoverflow question, but replication is not happening.

On the master:

State: Has sent all binlog to slave; waiting for binlog to be updated

When I show slave hosts:

+-----------+------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+------+------+-------------------+-----------+
|        51 |      | 3306 |                 0 |        23 |
+-----------+------+------+-------------------+-----------+
1 row in set (0.01 sec)

On the slave:

State: Slave has read all relay log; waiting for the slave I/O thread to update it

show slave status

           Slave_IO_State: Waiting for master to send event
 ...snip...
         Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 98
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 244
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

Is there something I need to do to get replication happening?


texts: Prove or disprove that `T=R÷S` implies `S=R÷T`?


texts: Why would splitting up a query make it faster, and can I/should I fix this?

texts:
The following query is for a web-based game.  It sets the isTiedForHighScore column to true for all solutions that tied the score for a given "map."

UPDATE solutions
INNER JOIN
(
    SELECT mapID, score
    FROM solutions AS s2
    WHERE isHighScore = 1
) AS maxScore ON maxScore.mapID = solutions.mapID AND solutions.score = maxMoves.score
INNER JOIN maps ON maps.ID = solutions.mapID
SET solutions.isTiedForHighScore = 1
WHERE maps.mapExpireTime >= @mapExpireTime
AND maps.mapExpireTime <= NOW()

(solutions.isHighestScore = 1 will only be true for exactly one solution for each map)

If I set @mapExpireTime to be 1, 2, or 3 months ago, this query runs in around 0.1s.  However, if I set it to 4 months ago or longer, it takes about 50s!

Why would this happen (there's no large difference in the size of the dataset over the months)?  I imagine it has something to do with all the data not being able to fit in memory - is this usual?

More importantly, is it standard practice to sometimes split a query into chunks (I could, say, send multiple queries, each one working on only one month's worth of maps at a time)?  Or is there some way to tell MySQL itself that it's allowed to work on this query in chunks?  I really have no idea how to speed up this query any more.

Here is the output of the EXPLAIN (I have to change the UPDATE to a SELECT to get this to output):

id    select_type     table       type    possible_keys       key           key_len  ref                            rows     Extra
1     PRIMARY         <derived2>  ALL     NULL                NULL          NULL     NULL                           1462
1     PRIMARY         solutions   ref     mapIDuserID,mapID   mapID         8        maxScore.mapID,maxScore.moves  3
1     PRIMARY         maps        ALL     mapExpireTime       NULL          NULL     NULL                           2953     Using where; Using join buffer
2     DERIVED         s2          ref     isHighScore         isHighScore   1                                       1585     Using where; Using temporary; Using filesort


texts:
How to change definition of a Postgres 12 GENERATED column?

CREATE TABLE test (
    id serial PRIMARY KEY,
    val_a int4 NOT NULL DEFAULT 0,
    val_b int4 NOT NULL DEFAULT 0,
    val_sum int4 generated always AS (val_a + val_b) stored
);

I tried

ALTER TABLE test ALTER COLUMN val_sum TYPE int4 generated always AS (val_a + val_b + 1) stored;
ALTER TABLE test ALTER COLUMN val_sum SET generated always AS (val_a + val_b + 1) stored;

but both give syntax errors.

The alternative is to drop & add column, which works, but I wonder how to simply change it like any other column.


texts: Username length restriction in different MySQL environments

texts:
I am setting up a project to my local environment and at its initial script are some SQL commands for creating the database:

create database 'db-name' default character set utf8;

and to grant privileges for the user account:

grant select, insert, update, delete, create, drop, index, alter on 'db-name'.* to 'user-name-too-long-for-user-name'@'localhost' identified by 'user-name-pass'

When I am trying to run the grant command I get this:

ERROR 1470 (HY000): String 'user-name-to-long-for-user-name' is too long for user name (should be no longer than 16)

and I can't understand why is this happening whereas this same script has successfully run to another environment before it comes to mine.

The server version I am using is 5.6.20 MySQL Community Server (GPL).

Is this situation related to a certain MySQL version or something else?


texts:
For the sake of not asking 5,000 questions here; are there any reliable sources of information about ERwin?

I have to use it for a course and I have been unable to find any website or documentation that is freely available.

Are there any documentation or resources available that cover basic usage of ERwin and how to apply everyday database admin concepts to it?


texts: How to purge logs by using Flashback feature in Oracle Database

texts:
I’ve configured flash recovery area for Oracle Database. My question is: how can I delete Flashback Database log?or is there any retention policy for that?

2- After I’ve enabled archive log mode and flash recovery area ,does purging automatically archived logs which are located archive log mode folder(not FRA logs)?or do I have to backup them via RMAN?

Thanks,


texts: How can I determine how many IOPs I need for my AWS RDS database?

texts:
I am migrating part of a mysql database into AWS.  The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints.

I estimate that we will need between 20-25k records inserted/hour during peak times.

I ran iostat -h on the current database, and it reported around 40 tps.

How do I figure out what type of IOPS I'll need?


texts:
I have watched many sample videos in youtube.com and discovered that in this videos in windows failover cluster dashboard configuration looks like this :

Quorum Configuration: Node and Disk Majority (Cluster Disk 1)

But on my dashboard it looks like:

Witness: Disk (Cluster Disk 1)

What is the cause of this difference. I am using Windows Server 2012 R2 Standard Edition


texts: SQLITE: Multiple foreign key referenced to multiple table cascade delete

texts:
In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where satisfied.

I have four tables.

TableA:
id, primary key
name

TableB:
id, primary key
issues,
tb_aid, #foreign key ref to TableA id
tb_cid, #foreign key ref to TableC id
tb_did, #foreign key ref to TableD id
tb_eid, #foreign key ref to TableE id

TableC:
id, primary key
column1,
tb_bid, #foreign key ref to TABLE B id

TableD:
id,
name

TableE
id,
name

I tried JOIN but unfortunately its not working in SQLite. I dont know how to use TRIGGER. Is it possible to use ON CASCADE DELETE for the above case.


texts: Aggregating two queries and returning a COUNT of some permutations

texts:
Hopefully the title can make sense to somebody, so I can get somebody working with a database (BigQuery in my case) to look into into.

Given two quarters, I need to find out the # of orders placed by each consumer for each of the two. I need also to count how many consumer I have in Q3 vs Q4 and do some calculation on the consumer retention.

I wish I could obtain the majority of this numbers with just one query, thus avoiding a number of queries.

I'll paste below a query in the attempt to explain what I need

  SELECT T1.email , T2.email
  FROM
  (
          SELECT email, 1 AS KEY
          FROM [reporting.order]
          WHERE 1 = 1
          AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-07-01 05:00:00"))
          AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
          AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
          AND name NOT LIKE '%test%'
          AND restaurantId NOT LIKE '%test%'
          GROUP BY email
  ) T1
  JOIN (
          SELECT email, 1 AS KEY
          FROM [reporting.order]
          WHERE 1 = 1
          AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
          AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2015-01-01 05:00:00"))
          AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
          AND name NOT LIKE '%test%'
          AND restaurantId NOT LIKE '%test%'
          GROUP BY email
  ) T2
  ON T1.KEY = T2.KEY

By adding an outer SELECT, with the above results I was able to count how many emails are in Q4 that were in Q3 (SELECT COUNT(CASE WHEN (T1.Email = T2.Email) THEN 1 ELSE NULL END) AS InPrevQ FROM) but anything more than this.

I thought to use the WINDOW function, but I'm struggling to understand its usage.


texts:
I have a user relation to address (1 to 1).

So the address has two types which is a type column.

One is the VerifiedAddress and the other one is the SearchAddress.

So the user who had verified his address has two entries in the addresses table.

In case you wonder why is that, the reason is that user can go to a travel and change the SearchAddress but the VerifiedAddress remains the same.

I'm trying to find all the users who haven't got verified their address. I have some user list from which I know who has verified address and who hasn't got it.

This is what I have tried the last, not exactly what I wanted :

select id from addresses
where user_id in (8112, 18620, 23, 19668, 10212)
and type = 'VerifiedAddress';

Gives me all the verified users, but if I change the type != VerifiedAddress I still get the same results because users who have verified their address have two entries in a addresses table, this time I get the user_ids from the SearchAddress.

How can I get this data from addresses table?

Note: SearchAddress can be null as well as VerifiedAddress. Naturally people who don't have searchaddress don't have the verified as well.


texts: How do I reclaim reserved space in the sysjobstepslogs table

texts:
I have a 36GB msdb database in my SQL Server 2008 R2 database server.  From what I have read this is past the point of being gigantic.  When I look at the tables in the msdb database the sysjobstepslogs table is using 97% of the space in the database.  I've done enough research to find out that the cause was several jobs had steps that had the Log to table option set + long running transactions + snapshot isolation on the msdb database.  This option has been disabled on those jobs.

I have tried to reduce its size by doing a truncate table, reindex, reorganize, check table, shrink file, shrink database (pretty much in that order) but with 0 rows, the sysjobstepslogs table still indicates that is has 35+GB reserved and used.

It has been suggested that I disable then reenable snapshot isolation to try to free up the space but according to this MSDN forum discussion
you cannot disable snapshot isolation on the msdb database.

I have contemplated dropping and recreating the table but how do I recreate the table as a system table and even if I did, is such a thing wise?

Any ideas how to reclaim this space?


texts: Change referenced index for a Foreign Key

texts:
I have something like this:

CREATE TABLE T1 (
    Id INT
    ...
    ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id]
)

CREATE TABLE T2 (
    ....
    ,T1_Id INT NOT NULL
    ,CONSTRAINT [FK_T2_T1] FOREIGN KEY (T1_Id) REFERENCES T1(Id)
)

For performance (and deadlock) reasons I created a new index on T1

CREATE UNIQUE NONCLUSTERED INDEX IX_T1_Id ON T1 (Id)

But if I check to which Index references the FK, keeps referencing to the clustered index

select
    ix.index_id,
    ix.name as index_name,
    ix.type_desc as index_type_desc,
    fk.name as fk_name
from sys.indexes ix
    left join sys.foreign_keys fk on
        fk.referenced_object_id = ix.object_id
        and fk.key_index_id = ix.index_id
        and fk.parent_object_id = object_id('T2')
where ix.object_id = object_id('T1');

If I drop the constraint and create again it references the nonclustered index, but this lead to check all t2 FK again.

Is there a way to change this so the FK_T2_T1 uses IX_T1_Id instead of PK_T1 without dropping the FK and locking the table on FK checking?

Thanks!


texts:
I have a job that syncs rows in a table between a production server and four other servers.

The problem is the job runs perfect on three servers, taking less than 2 seconds. On the fourth server, it takes over 12 seconds which causes a delay. There are no apparent indexing or statistics issues.

I talked to our systems administrator, he says the outbound packets are fine, but the problem is when the information goes back to the production server. The packet comes in 4600 bits and the network the server is using only 1600 bits and it takes time to divide the packets. He is asking if there is a way to do something in SQL Server to manage that, we told him no.

Has anyone faced this problem before? The trouble server is new and more powerful than the other three.


texts: How to connect client applications to SQL SERVER 2012 cluster?

texts:
Following are the environment Details :

Database Server : SQL SERVER 2012
Operating System : Windows Server 2012

Single instance of SQL SERVER 2012 is installed in cluster configuration with 2 nodes. I have 3 IP addresses, one for each node of SQL SERVER and one for the cluster.

I am able to connect to this configuration by providing SQL SERVER instance name to the DataSource of connection string but i am unable to connect using cluster name as data source

so the following is working

<add name="Default" connectionString="Data source=SQLInstanceName;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

but changing SQLInstanceName to IP address/Name of cluster does not work

<add name="Default" connectionString="Data source=clustername;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

Is this by Design?

My understanding is that client application needs to connect to cluster and not to specific instance.


texts: Write performance of Postgresql 9.1 with read-only slave

texts: I have a Postgresql 9.1 database that is being hosted by Heroku.  It currently has a read-only "follower".  I need to truncate several large tables (over 100GB) of data and reloads them.  Is the read-only follower going to cause an additional overhead to this operation?  Should I drop the follower and recreate it after the process is complete?


texts: Filtered index error when using two column condition

texts:
I created a filtered index (in SQL Server 2012)  and compared it with non-filtered index – I could see promising improvement as shown below. Now I need to change the filter condition to use two columns in the table. The required condition is  WHERE InboundQuantity - OutboundQuantity <> 0 or  InboundQuantity <> OutboundQuantity

But when I apply this filter, I am getting error message

  “Incorrect WHERE clause for filtered index 'IX_WO_PlantCD_FilterQtyNotEqual'”.

I know there are limitations with filtered index. However is there a way to achieve this improvement with two column condition, using filtered index or the like?

Query

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0

--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

Plan


texts: pgpool-II doesn't execute my failover script

texts:
I have been installed pgpool-II 3.3.4 from source. I have two pgpool nodes and I also used the watchdog configuration to get a pgpool services with High Availability. When I stop one, the other wake up and everything is fine.

Also I have two backends, one master and one slave, then I have configured the failover command. Here is my problem, when I stop the master, pgpool doesn't execute the failover.

There are my backends:

backend_hostname0 = '172.23.0.70'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.3/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '172.23.10.21'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.3/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

There is my failover command declaration:

failover_command = 'failover.sh %d %M %m'

Of course, I have been copy the failover to /sbin/ directory in pgpool nodes.
There is the failover.sh script code:

#!/bin/sh

FALLING_NODE=$1

# The new master
SLAVE1="172.23.10.21"

if test $FALLING_NODE -eq 0
then
ssh -T postgres@$SLAVE1 "touch /tmp/postgresql.trigger.5432"
ssh -T postgres@$SLAVE1 "while test ! -f /var/lib/postgresql/9.3/main/recovery.done; do sleep 1; done;"
ssh -T postgres@$SLAVE1 "/etc/init.d/postgresql restart"
/usr/local/bin/pcp_attach_node 10 localhost 9898 pgpool pgpool 1
fi

Now, when I run the failover.sh manually, it works, so why it doesn't run with pgpool?

I run pgpool with sudo user so:

Do I need to run pgpool with other user?
Do I need to create an specific user to pgpool configuration? It works manually with sudo!!!.
Do I have to change my failover script command?


texts:
I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000

CREATE TRIGGER TRG_UPDATE_STATUS
AFTER UPDATE ON product
FOR EACH ROW
  BEGIN
    IF NEW.QuantitySold >= 1000 THEN
    SET NEW.Label = 'BestSeller'
    END IF
  END

Here is the trigger I have.


texts: Why is SQL Profiler showing empty for database name and database id?

texts:
I have some code that uses Entity Framework to call a stored procedure in SQL Server. If I run SQL Profiler and filter by the database it is calling, I can see that the profiler correctly shows the call.

However, the database name and database id column are always empty. Anyone know why it is not showing this information?

I'm using this version:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011
  00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1)


texts: SQL Server Management Studio 2014 12.0.5000.0 "Invalid Column Name" Error

texts:
The following code executes as expected.  However, the editor shows red lines under attribute5 and WCC_ID in "h.attribute5 = i.WCC_ID" and states "Invalid column name" when the mouse hovers over the red lines.  These columns exist in the respective tables.

What is wrong with the SQL?

SELECT
*
FROM
(
SELECT attribute5
FROM
OPENQUERY([FAMISPRO.WORLD],'SELECT attribute5 FROM FAMIS_EMP')
) AS h
INNER JOIN
(
SELECT DISTINCT h.WCC_ID, h.KEYMARK
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' ) AS h
LEFT JOIN
(
SELECT *
FROM
OPENQUERY ( PROJECT ,'SELECT * FROM ENTRY;' )
WHERE
EVENT = 'RETURNED'
) AS i
ON
h.WCC_ID = i.WCC_ID AND h.KEYMARK = i.KEYMARK
WHERE
i.EVENT IS NULL
) AS i
ON
h.attribute5 = i.WCC_ID;


texts: Should I revoke login from the default pgsql user?

texts:
I set up a localhost pgsql installation (os x catalina, homebrew, 12.*). I am developing a sql data warehouse and I’d like my dev environment to be as close to the production one as possible. Homebrew created a default superuser, chris. Is it ok (and possible) to revoke the login attribute from this user? I have set up dedicated users to interact with the data warehouse and the actual data in the system.
Thanks!


texts: Grouping Based on Column Consecutive Values

texts:
I have table with let say the below 3 columns

Date                    OfferId         Title
4/29/16 - 5/19/16       123             title1
5/20/16 - 5/30/16       123             title2
5/31/16 - 6/2/16        123             title3
6/03/16 - 6/13/16       123             title1
6/14/16 - 6/16/16       123             title4
6/17/16 - 6/20/16       123             title2
6/21/16 - 6/25/16       123             title4

i have some other columns which need sum

Where i put range like this 4/29/16 - 5/19/16 it means i have multiple rows for those dates with the same title.

as you can see the

title1 is same between 4/29/16 - 5/19/16 and 6/03/16 - 6/13/16
title2 is same between 5/20/16 - 5/30/16 and 6/17/16 - 6/20/16
title4 is same between 6/14/16 - 6/16/16 and 6/21/16 - 6/25/16

So when we do grouping on Title so what it does is return 4 results

select Min(Date),OfferId, Title from temp group by Title;

using the above query the results will be

Min(Date)   OfferId         Title
4/29/16     123             title1
5/20/16     123             title2
5/31/16     123             title3
5/31/16     123             title4

what i wanted is it should be look like this so i can see all the history the title got changed.

Min(Date)   OfferId         Title
4/29/16     123             title1
5/20/16     123             title2
5/31/16     123             title3
6/03/16     123             title1
6/14/16     123             title4
6/17/16     123             title2
6/21/16     123             title4

How can I achive this


texts: How to implement business logic permissions in PostgreSQL (or SQL in general)?

texts:
Let's assume I have a table of items:
CREATE TABLE items
(
    item serial PRIMARY KEY,
    ...
);

Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking about database access permissions here, but business logic permissions for that item). Each item has default permissions and also per-user permissions that may override default permissions.
I tried to think of several ways to implement this and came up with the following solutions, but I'm not sure on which is the best and why:
1) The Boolean Solution
Use a boolean column for each permission:
CREATE TABLE items
(
    item serial PRIMARY KEY,

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),

    PRIMARY KEY(item, user),

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

Advantages: Each permission is named.
Disadvantages: There are dozens of permissions which increases the number of columns significantly and you have to define them twice (once in each table).
2) The Integer Solution
Use an integer and treat it as a bitfield (i.e. bit 0 is for can_change_description, bit 1 is for can_change_price, and so on, and use bitwise operations to set or read permissions).
CREATE DOMAIN permissions AS integer;

Advantages: very fast.
Disadvantages: You have to keep track of which bit stands for which permission in both the database and the front-end interface.
3) The Bitfield Solution
Same as 2), but use bit(n). Most likely the same advantages and disadvantages, maybe slightly slower.
4) The Enum Solution
Use an enum type for the permissions:
CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

and then create an extra table for default permissions:
CREATE TABLE item_default_permissions
(
    item int NOT NULL REFERENCES items(item),
    perm permission NOT NULL,

    PRIMARY KEY(item, perm)
);

and change the per-user definition table to:
CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),
    perm permission NOT NULL,

    PRIMARY KEY(item, user, perm)
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).
Disadvantages: Even when just retrieving the default permissions, it requires accessing two additional tables: first, the default permissions table, and second, the system catalog storing the enum values.
Especially because the default permissions must be retrieved for every single page view of that item, the performance impact of the last alternative might be significant.
5) The Enum Array Solution
Same as 4), but use an array to hold all the (default) permissions:
CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

CREATE TABLE items
(
    item serial PRIMARY KEY,

    granted_permissions permission ARRAY,
    ...
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).
Disadvantages: Breaks the 1st normal form and is a bit ugly. Takes up a considerable number of bytes in a row if number of permissions is large (about 50).
Can you think of other alternatives?
Which approach should be taken and why?
Please note: this is a modified version of a question posted earlier on Stackoverflow.


texts: Mysql Update Query is not Giving Desired Result

texts:
In My Php Code I Have Query As

SELECT COUNT(id) AS todayleads
FROM `lead_data`
where `date` = '$todate' AND lead_status = 1
AND uid = '$userid' OR assign_to = '$userid'

When I echo Query it result me

SELECT COUNT(id) AS todayleads
FROM `lead_data`
where `date` = '2018-01-12' AND lead_status = 1
AND uid = '3' OR assign_to = '3'

And Output Gives Mea All Data From Start to End. But Expected O/p is Only Data That have lead_status = 1 and uid = 3 Or  assign_to = 3.


texts: Can old collation type impact database performance?

texts:
In my project we use SQL_1xCompat_CP850_CI_AS as collation setting for SQL Server instance and for our application's databases. As far I know this is one of very old collations still supported just for backward compatibility purposes. I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

In addition, is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_Cp850_CI_AScollation?
Description on MSDN looks very similar:
Both are CI_AS_KI_WI, use code page 850, and have the same sort order name nocase34.850.
Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_Cp850_CI_AS?


texts: Possible to restore a damaged database's PRIMARY filegroup only?

texts:
I have a database (MyDatabase) with 2 filegroups, [PRIMARY] and [SECONDARY].

The [SECONDARY] filegroup has 2 datafiles, [DataFileA] and [DataFileB].

[DataFileA] is damaged (even after DBCC CHECKDB process).

I tried to solve it with:

DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) but it didn't help.
Deleted the damaged datafile [DataFileA], with no success.

The [PRIMARY] filegroup is ok though.

Can I backup just the [PRIMARY] filegroup and restore it (under a new name) and create a new database from this single filegroup?


texts:
We are planing to test the performance of the new DB server. For that we have the below the plan:

get production sql queries
replay those sql queries against the different DB

I was planing to use pt-log-player to play the sql queries and use the pt-query-digest to analyze the result files which gets generated from pt-log-player after execution. So my questions are:

how to get the production query in the first place. enabling slow log query is not an option due to performance reason (as our DB admin denied about that)
Why pt-log-player is deprecated, was there any bug or whats the reason behind.
Or someone can suggest me a complete different toolset to do the perf test. Thanks.


texts:
I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that table, to compute a column called for Active. This is referenced throughout the application to return records from the database in the where clauses.

I noticed this is slowing down each select of the system. I'm assuming GETUTCDATE, just like an article I saw from you saying don't use a Scalar function in a computer column, is just as bad. Do you have a recommendation on what to do here. I've always maintained an actual column of active, and built services to activate as necessary, which would obviously still be hitting sql, but might not effect the front end user interface.


texts: When was the stored procedure created and altered?

texts:
I am about to update a MySQL server in operation with the changes made on a development server.
I want to be sure that I don't miss any changes.
Are there any logs telling things like when the stored procedure was created and altered om a MySQL server?
I read the documentation about server logs but it does not seem to cover this issue.


texts: Please explain how this query generates a calendar table in MySQL

texts:
This page has a pretty neat way of generating a robust calendar table. I've copy-pasted it below:

CREATE TABLE calendar_table (
    dt DATE NOT NULL PRIMARY KEY,
    y SMALLINT NULL,
    q tinyint NULL,
    m tinyint NULL,
    d tinyint NULL,
    dw tinyint NULL,
    monthName VARCHAR(9) NULL,
    dayName VARCHAR(9) NULL,
    w tinyint NULL,
    isWeekday BINARY(1) NULL,
    isHoliday BINARY(1) NULL,
    holidayDescr VARCHAR(32) NULL,
    isPayday BINARY(1) NULL
);

CREATE TABLE ints ( i tinyint );

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO calendar_table (dt)
SELECT DATE('2010-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 11322
ORDER BY 1;

I don't understand what is happening in the last query. Can someone explain it to me step by step?


texts: Query "last" records in a table with non-sequential key fields

texts:
My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task.

The problem is that this table has around 70 million records (~100GB of data, including indexes), and I can't just drop it then load it again, because it is too resource intensive (especially because other tasks must be done).

The obvious solution would be an incremental update: I query the newly inserted records from Oracle and insert them into SQL Server.

So far, so good.

But the table I am querying from in Oracle has no ordering at all. Its unique key is composed of four fields that can receive any value, and I have no control over them.  I must ensure that only "new" records are returned, but there is not a field (or set of fields) that I can use to sort the table and 'force' a sequential ordering. To be clear: none of the columns stores any kind of CreationDateTime datum.

I have only SELECT permissions on the Oracle database, so I can't make any changes, for example to flag records I've already imported.

I can connect to both databases from the same computer.

Querying the destination database for each record is also very resource intensive. Network and disk overheads are a problem, but they are not my main concern here.

Has anyone faced this problem before? Any idea how to solve it? Maybe there is no solution for this problem, but I was expecting some sort of 'trick': some hidden Oracle column, internal procedure, or other dark magic...?

My last resort is to make some assumptions to import, like "assume these records are ordered and import, then check consistency of some well chosen samples, if inconsistent, run full import again". This is very far from ideal, but at least I will not run a full import every time.


texts: Should my database be on the same server as my web server?

texts:
We are about to add user login and management to a high traffic website.  I am wondering if I should put the postgres database on the same server or put it in a different node in the internal networked setup.

Reason I have been thinking of this is I believe the database would be more secure if it was only accessible via internal networking.


texts: Fixing a corrupt Log File in small downtime window

texts:
I am working with a client who has been receiving the following error from their Log backups of one of their SharePoint databases:

Backup detected log corruption in database FOOPORTAL_SP2010_Config. Context is FirstSector.
  LogFile: 2 'D:\MSSQL2K8\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FOOPORTAL_SP2010_Config_log.LDF'
  VLF SeqNo: x864 VLFBase: x172d0000 LogBlockOffset: x176b1800
  SectorStatus: 2 LogBlock.StartLsn.SeqNo: x771 LogBlock.StartLsn.Blk: x1f0c
  Size: x400 PrevSize: x200

I understand that this is indicative of problems with the disk HW and or IO subsystem, and we have been pursuing that with the Hosting Provider.  Specifically, this appears to be a "bad spot" on the/a disk, because it is consistently reported for the log file of this DB, but not other errors have been reported for the other 6 DB log backups over the last few week, nor for any of the 20+ other DB's full data backups.

My immediate issue is that the client has been running this way for several weeks, because this is a mandated 24x7 service and they haven't been able to give me a downtime service window to fix this problem until (suddenly) tonight, for 2 hours.  Unfortunately, DB corruption and this kind of Admin work is not my specialty in SQL Server, and I am not certain what the best/safest/most reliable way to proceed is.

My tentative plan right now is:

Take a full backup of the DB as soon as the downtime window starts (full backups have been working without error through out this)
Detach the old DB
Delete the data files for the space
Rename the Log file (still retaining it)
Restore a new copy of the DB from the fresh backup from step 1.

My concerns/anxiety over this are:

Should this work?  Or is htere a safer/more reliable approach?
I only have 2 hours, so is there a more time efficient approach?

Any other suggestions are welcome.


texts: Indexed view not used in actual execution plan?

texts:
I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table:
CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING
AS
    SELECT [Category], [Year], SUM(ISNULL(Loss,0)) AS [Loss], COUNT_BIG(*) as [Count]
    FROM dbo.Sub_Portfolio_Losses
    GROUP BY [Category], [Year]
GO
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio([Category], [Year]);

My original goals were more ambitious, but indexed views are so restrive... I'd be happy enough just to get this to work.
Sadly, when I try to do a basic query on this indexed view (SELECT TOP (100) * FROM Losses_CombinedPortfolio ORDER BY Loss DESC), the query is just as slow, and the actual execution plan shows that it's always going back to the source table and computing the aggregations from scratch each time:

I can only assume that this is because my computed "Loss" column isn't getting materialized - but that would surprise me, since the clustered index creation succeeds.

Note that my primary use-case for this view is to sort by Loss descending, but I couldn't manage to explicitly create an index that includes it:
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio(Category, Loss DESC, [Year]);

I get the error:

Cannot create index or statistics 'Idx' on view 'dbo.Losses_CombinedPortfolio' because key column 'Loss' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.

I tried resolving the "imprecise" by casting the summed loss to types other than float (even tried truncating it to bigint) but it seems this error is stemming from the underlying type used to compute the sum.
I'm confused - I've seen other questions claim that they were able to successfully perform aggregations such as sum in their indexed views, so I'm not sure why this wouldn't work.


texts:
My procedure giving numeric overflow error:

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196

because it exceeds pls_integer maximum value +2,147,483,647. How to overcome this error?
I have to purge 2234202989 rows. So I am getting error.

sp_log_message (LOG_ERROR, 'sp_instrumentationdata', lsCurrentTable, 'Exception', NULL, SQLERRM);

Here it is showing .. This table having starttime and endtime two .. date fileds .. I am using endtime .. I find out max.endtime .. min.endtime and max-min(endtime). I got 144 days .. I have given package.sp_prc(keep days); exec hdm_cleanup_util.sp_instrumentationdata(143). Means i am going to purge Just one day data. whenever I execute the above command .. immediately I am getting ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 .. error.


texts: MySql view returns a single NULL value for a NOT NULL column, why?

texts:
I am working with a database built by a vendor, which I have remote access to. I noticed that the field modify_date on one of the views is not null, according to DBeaver.

the tick indicates "not null".
and indeed running this query
select *
from list l
where l.modify_date is null

produces zero records.
However, running this query
select *
from list l
order by l.modify_date

produces the following.

As you can see, there is a single NULL record.
Also, this query
select l.modify_date, count(*)
from list l
group by l.modify_date
order by l.modify_date

shows a count of 1 for null records.
Why do some queries return a NULL column here, but the column properties show it as NON NULL and trying to select NULL values returns zero records?
UPDATE
I forgot that I had taken a local copy of the view data. To do this, I examined the information_schema of the remote db and got the column names and properties. I used this to generate a 'create table...' statement locally. I extracted the view data as a csv and loaded the data with 'load data local infile ...'.
My create table statement looks like
CREATE TABLE if not exists Bloom.list (
    some columns ...
    modify_date timestamp NOT NULL,
    some more columns ...
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;


texts: Do you connect an application to the load balancer name or the cluster name?

texts: My DBA delivered a 2 server WSCF for me to connect my sharepoint farm to.  I have a load balancer name and a windows cluster name, and 2 server names.  I realize I need to connect either to the cluster name or the load balancer name but which would be the correct choice and why?


texts: Oracle TimesTen vs Oracle database in-memory

texts:
Oracle has recently announced its latest Oracle database in-memory feature in 12c.

Oracle already had an in-memory database TimesTen and now I was wondering as to what the the differences between these two beasts?


texts: Foreign Key problem, ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option

texts:
my main problem is when I create these tables particularly the Member table, it does not add the foreign key constraints for the other tables created, I keep getting errors: ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option (if using the alter table options below the table creation, I am using oracle application express (apex.oracle.com)

I have searched other questions regarding this, but they were mostly related to stored procedures or querying a table, nothing with table creation.

CREATE TABLE MemberAddress (
 MemAddID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 Address VARCHAR2(60) NOT NULL,
 SecAddress VARCHAR2(60),
 PostCode VARCHAR2(8) NOT NULL,
 City VARCHAR2(40),
 CONSTRAINT MEM_ADD_PK PRIMARY KEY (MemAddID)
);

CREATE TABLE MemberType (
 MemTypeID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemType VARCHAR2(10),
 AccountStatus VARCHAR2(20),
 CONSTRAINT MEM_TYPE_PK PRIMARY KEY (MemTypeID)
);

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID)
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE
);

I used these alter table options to test whether the SQL could add the foreign key out of table definition, however I get the error ORA-01735: invalid ALTER TABLE option.

-- ALTER TABLE Members ADD CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE;

-- ALTER TABLE Members ADD CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not new to SQL or a pro at it, if there is a best practise im not following here, let me know?

I do not understand what is wrong with this; whether its a syntax problem or oracle application express has problems, in MySQL it works great when the syntax is changed to suit that DB.

any suggestions?


texts:
I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this against, it returns indexes with 0 scans, 0 seeks and 0 lookups and there are some indexes that just returns NULL on the scans, seeks, lookups column. My understanding to those NULL's is that, that specific index has still not been used that's why there is no data for that index in sys.dm_db_index_usage_stats. That being said, whats the difference between these and those indexes that show up in sys.dm_db_index_usage_stats but has 0 scans, seeks and lookups? I appreciate everyone's ideas

DECLARE @tblname VARCHAR(100) =  'Login' --<<<TABLE NAME HERE

SELECT CONCAT(OBJECT_SCHEMA_NAME(i.[object_id]), '.', OBJECT_NAME(i.[object_id])) AS [OBJECT NAME]
       ,CASE
           WHEN I.name IS NULL THEN
               'heap table'
           ELSE
               I.[name]
       END AS [INDEX NAME]
      , i.index_id
      , s.user_seeks +s.user_scans + s.user_lookups AS [Total Scan/seek/lookup]
      ,s.user_seeks
      , s.user_scans
      , s.user_lookups
FROM sys.indexes AS I
     LEFT JOIN  sys.dm_db_index_usage_stats AS S
        ON  I.index_id = S.index_id
        AND I.[object_id] = S.[object_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
      AND OBJECT_NAME(i.object_id) = @tblname
      --AND i.index_id <>1
      ORDER BY s.user_lookups DESC


texts: PostgreSQL: Table and index on different tablespace

texts:
In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself.

Is there any performance impact of this?


texts: Finding row with Max value with inner join and sub-query

texts:
I need to SUM the total values of each student using INNER JOIN and then find the row with maximum value.

I have used the below query to find the SUM of each student and sort in descending order SHOWING ALL the students including the one with the maximum value.

Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
from        Student S
inner join  Attempt A
on          S.Student_ID = A.Student_ID
group by    S.Student_ID, S.Student_Name
order by    3 desc;

I need to ONLY show the student with the maximum value but it shows an empty table.

Select     S.Student_ID,
           S.Student_Name,
           MAX(A.Score) as "TOTAL SCORE"
from       Student S
inner join Attempt A
on         S.Student_ID = A.Student_ID
where      A.Score = (select   SUM(A.Score)
                      from     Student S)
                      group by S.Student_ID, S.Student_Name;`

Does this have to be done with MAX or is there another way to do it, if latter then how.

Not sure if I can explain simpler than this.


texts: Why should foreign keys be implemented in my relational database?

texts:
Earlier today, I sparked a rather unexpected debate with my professional peers on the topic of foreign keys in our company's databases. In a nutshell, a software engineer who is senior to me stated that he didn't like a commit I had made to one of our repositories because I implemented a foreign-key relationship between two tables. His argument amounted to him not liking foreign keys because they restrict the data that can be inserted into the database & he believes that makes the database harder to work with. (Note: his argument was against foreign keys in general, not just the anecdotal foreign-key constraint that I proposed in our app. He believes that foreign-key constraints are a design flaw in modern relational database systems.)

At this point, I was caught completely flat-footed, and our boss chose to side with the senior developer for the time being, asking me to remove the foreign keys from my implementation of the bug-fix I was working on. The bug that I was fixing in fact stemmed from relational inconsistencies that were confusing our ORM (which itself leaves a bit to be desired, but that's a different story), and while I tried to explain this to my team, everyone sided with the senior dev & I was told to go back to the drawing board and re-implement my database fix WITHOUT relying upon foreign key constraints. In other words, I've been asked to do relational management without the built-in relational-management tools of our RDBMS (posgres, if it matters).

I reiterated that this was not the proper way to go about fixing the bug, at which point my boss told me that the discussion was taking up too much time and that if I wanted to debate the merits of formal FK-constraints, I would need to do it at a later date... which I intend to do, but I also feel like I've been tasked with arguing why water is wet.

When I go into this debate, I want to come armed with as many facts in support of foreign-key usage as possible. I know that implementing sensible FK-constraints:

serves as an automatic guardrail against relational inconsistencies (ie, prevents "garbage data" (which is a demonstrable problem with the system I'm working on currently)),
clarifies the high-level relational context between two related tables to someone who is inspecting the database schema, and
can increase the performance of database query operations.

... But I'm wondering if there are any other bullet points that I may be missing on this topic with which I should meet this debate. What other benefits should I make sure to bring up with my boss and colleagues? (Or has there genuinely been a paradigm shift away from FK-constraints & I've been living with my head under a rock when it happened?)


texts: Can't create schema from `assumed` role with ALL privileges

texts:
For the postgresql 10 have this code:

CREATE DATABASE featurestore WITH OWNER admin;
CREATE ROLE editor WITH LOGIN PASSWORD XXXX NOINHERIT NOCREATEROLE;
CREATE ROLE fstore_migrations WITH NOLOGIN NOCREATEROLE;
GRANT fstore_migrations TO editor;
GRANT ALL ON DATABASE featurestore TO fstore_migrations;

AND then log in as editor, and

SET ROLE FSTORE_MIGRATIONS;
SELECT current_database()               # returns featurestore
SELECT SESSION_USER, CURRENT_USER;      # returns editor, fstore_migrations
CREATE SCHEMA IF NOT EXISTS BUILDINGS   # (psycopg2.errors.InsufficientPrivilege) permission denied for database featurestore

What did I miss? Is there any way to see permissions on database (rather than specific tables/schemas ?


texts: MYSQL - Error in After Insert trigger affects the actual insert on the trigger table

texts:
I have two tables call it - table_1 and table_2

I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3.

When the insert on table_2 fails, the table_1 insert fails.

Is this normal? and Is there a workaround for this?

Thanks


texts: Oracle not trusting statistics after an import?

texts:
We are seing some strange behavior in the way Oracle is handling statistics of an imported schema.
We build a schema on the dev system, analyze if and import into the production with the stats.
For the most part everything works fine, but some query seems to select different plans than in dev.  After re-analyzing them in production, the plan becomes the same as on dev.  The schema is not changed on prod (pretty much read-only).  So there must be some kind of difference between how Oracle handles stats built locally vs imported?


texts: How much of a security risk are published conceptual schema?

texts:
I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk.

I don't really have extensive database experience so I can't verify that claim. Is disclosing your schema really that big of a security risk? I mean, those are pretty abstract and divorced from the hardware and software implementations. An explanation of how an attacker could exploit conceptual schemas would be appreciated. Thanks.


texts:
I'm trying to automatically calculate a table for user retention, so that the end result is something like:

Week | %
0    | 100
1    | 50
2    | 35

For now I'm not bothering with the % and just trying to get a count of users, i.e. users that came back on week 1, week 2, etc.

I have an accounts table and an action table. Currently I don't have indexes in either of these. Both have a user_email that stores the associated user, and both have a timestamp field that's a TIMESTAMP WITH TIME ZONE - for accounts this is the account creation date and for action it's the timestamp of the action.

The two tables are currently super simple:

accounts Table:
timestamp  | timestamp with time zone
user_email |character varying

action Table:
timestamp  | timestamp with time zone
user_email |character varying

That's it, no keys or anything else.

I managed to piece together a query that seems to do this calculation:

WITH weeks AS (
    SELECT (n || 'week')::interval as week from generate_series(0, 3) AS n
)
SELECT

    week,

    (SELECT count(*) FROM accounts WHERE user_email IN
        (SELECT user_email FROM action
            WHERE age(action.timestamp, accounts.timestamp) >= week
            AND age(action.timestamp, accounts.timestamp) < week + '1 week'::interval))
            AS "Returning Users"

FROM weeks;

The output here is something like:

week    | Returning Users
0 days  |  100
7 days  |  50
14 days |  35
21 days |  25

The thing is, this takes super long to calculate (with 1300 users and about 100000 actions it takes a few minutes). I'm also convinced there's a more efficient way to calculate this, or that I can at least add indexes somewhere.

Unfortunately my SQL knowledge is quite limited and after looking around for a while I'm still not sure where to start. I think I can add indexes to the timestamp columns? And I'm not sure my way of checking if timestamps fall in certain ranges is correct.


texts: Return both one value and one column from pl/pgsql function

texts:
I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection:

one vector containing data whose type is unknown at call time (an array)
one string value containing information as how to interpret this data

Here is an illustration of the function architecture:
What should I use as a return type / return value / return instruction so both these informations are returned?

CREATE OR REPLACE FUNCTION public.retrieve_vector(
    vname bigint)  -- the vector name: actually a column name
    RETURNS RECORD -- * * * is that the return type I need?  * * *
    LANGUAGE 'plpgsql'
    VOLATILE
AS $BODY$
DECLARE
    info text; -- data interpretation information
BEGIN

-- Retrieve data information from, say, the such-information table
SELECT info FROM public."InfoTable"
    WHERE name = vname
    INTO info; -- (this is actually a more complicated process)

-- Retrieve the vector and return both
RETURN info,
      (SELECT vname FROM public."Vectors"); -- (this is also more complicated)
-- * * * but RETURN a, b; is not possible like this, right? How to do? * * *

END
$BODY$;

As dummy instances, I expect this function to return something like retrieve_vector('temperatures') -> ['old thermometer', [0, 1, 5, -3]] or retrieve_vector('words') -> ['small words only', ['at', 'the', 'and', 'or']].
(To those who might ask: in reality this is about meta-programming, so the "info" is actually refined type information.)

In such a situation, I think that:

it is legitimate that this function returns two values
it is legitimate that the vector type is unknown at call time
it is legitimate that the first value is scalar (single value) and the other is a vector (a column)
it is legitimate wanting to connect the database only once

Is it possible defining a function with such a flexible signature?
How would I do this?


texts:
Recently, I thought I was gettint into a Oracle Database Administration and Development project, with a heavy load of plsql programming in the form of stored procedures and such.

I was struck when, the other day, I was told I shouldn't rely so much on plsql as it can lead to undesired behavior on Production Systems, and that it would be ideal to perform all the necessary transformations on the data outside of the database, and use the database more as a repository than anything else (only insert intoand select should be used. Just store data, don't operate with it in any other way).

Not only that, but we were asked to do it all in Java (the face I made at that moment...). Since then I have cracked my head trying to figure out an easy way of satisfying these requirements and the best idea I could come of is trying to replicate a relational engine in a Java project. I know there are plenty of frameworks and libraries, like hibernate, that would somehow help me with the first steps of this project, but as you might have assumed, I could only use these to retrieve data from the database, not to pass to the db the queries that should be performed to make all necessary transformations (that would only be adding extra steps to the first, forbidden, scenario). I would still need to replicate operations like joins, unions, etc.

Here are my cries for help:

Is it true that plsql can lead to undesired behavior in Production Systems? (Oracle database)
Is replicating the work of the relational engine a sensible idea?
Are there any tools that can really help in a situation like this?
Should I just slap the genius behind this whole idea back to his senses?


texts: Normalization: Is it considered compliant to split static, numeric values like a year into their own table?

texts:
I am having an interesting discussion with another database designer about normalization.  In this example, we have a GameTitles table and each record must contain the year in which the game was released.  He says 2NF mandates that everything must be normalized, so, to be compliant, the year field should be split off into a ReleaseYears table with its own primary key that is referenced by the GameTitles table.  I say it should remain as a field on the GameTitles table itself.

My argument for this is that a year is just a non-primitive numeric value that is static by its very nature (ie, 2011 will always be 2011).  Due to this, it serves as its own identifier and needs nothing to reference it since it is what it is.  This also introduces additional maintenance since you now have to add a new year to the table just to reference it.  If you prepopulate the table with a large range of years then you have extra records that potentially won't have references to them at all.  This also increases database size since you now have an extra table, record overhead, and the additional primary key for the year itself.  If you keep the year as a field on the GameTitles table, you eliminate all this additional maintenance and overhead.

Thoughts on this?

edit: Meant to post this on StackOverflow.  Can someone vote to delete this or flag it for attention?


texts: Does PostgreSQL support `unnest` as a column?

texts:
Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column,

SELECT
  unnest(array_agg(id)) AS id
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1

If I try to move the unnest to FROM there, I get

ERROR:  aggregate functions are not allowed in functions in FROM
LINE X: CROSS JOIN LATERAL unnest(array_agg(id)) AS id

You can create the test data to run against this, like this

CREATE TABLE foo AS
SELECT row_number() OVER (), *
FROM ( VALUES
  ( 'X', 'D', 'G', 'P' ),
  ( 'F', 'D', 'L', 'M' ),
  ( 'X', 'N', 'R', 'S' ),
  ( 'Y', 'I', 'W', 'Q' ),
  ( 'U', 'Z', 'E', 'H' )
) AS f(a,b,c,d);

What's a better way to write the above?

Is there anything less convoluted than,

SELECT k FROM (
  SELECT array_agg(id) AS x
  FROM foo
  GROUP BY CUBE (a,b,c,d)
  HAVING count(*) > 1
) AS t
  CROSS JOIN LATERAL unnest(x) AS k;


texts: Column Level Encryption in SQL Server 2016

texts:
When using the column level encryption approach, do we need to explicitly close the symmetric key in stored procedures/views or its not required? how it's handled for the subsequent openings ?

eg:

CLOSE SYMMETRIC KEY SQLSymmetricKey


texts:
It appears the files in my /var/lib/mysql/mysql are borked, and I can only start mysqld with --skip-grant-tables.

Now I want to export all tables with mysqldump --all-databases, wipe and reinstall mysql, and re-import the tables.

Unfortunately, mysqldump seems to ignore the --skip-grant-tables option that I used to start mysqld. It always assumes a username even if I don't specify one (root or the current linux user), asks for a password, and then fails (because it cannot verify the pw due to the borked files).

So, how can I export tables and circumvent the credentials?


texts: Using Postgresql logical replication, how do you know that the subscriber is caught up?

texts:
Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are:

pg_stat_replication

and it's sibling:

pg_stat_subscription

I've read the documentation for these, but they don't say how to know if a replica is actually synced, and interpreting these tables didn't seem obvious to me. Can anybody explain?


texts: How to simulate postgresql recovery mode?

texts:
I would like to simulate putting postgresql into "recovery mode".
Few times i have situation where postgresql puted himself into recovery mode. In logs i had:

Oct 18 06:37:35 xx postgres[5738]: [19-1] 2014-10-18 06:37:35.441 CEST|5738|xx|yy|FATAL:  the database system is in recovery mode

It was caused by oom which killed one of process.
But when i try to simulate it in test environment - postgresql restarts.

How to prevent postgresql from restart and let him stay in recovery mode?


texts: Are cursors/While loops the only way to make administrative changes to multiple databases?

texts:
I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in PowerShell can solve this problem very easily:

Import-Module SQLPS
CD SQL\*ServerName*\*InstanceName*\databases
foreach ($database in (ls -force)){
$database.recoverymodel = 'Full'
$database.update
}

I'm looking for the most efficient T-SQL solution.
It has been ingrained in my head to avoid cursors at all costs, however I can't think of a set based solution to perform alter statements.
Here is the cursor based solution.

DECLARE @sql varchar(MAX), @name varchar(50)

DECLARE cur CURSOR FOR
SELECT name
FROM sys.databases
where name <> 'tempdb';
OPEN cur

FETCH NEXT FROM cur
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE ' + @name + ' SET RECOVERY FULL;'
    exec(@sql)
    FETCH NEXT FROM cur INTO @name
END
CLOSE CUR
DEALLOCATE cur

This can also be done with a while loop, but that solution still goes through a looping process... do while loops offer significantly better performance?
Am i being paranoid about performance? I say that because administrative tasks such as this aren't done very often, and there generally isn't a massive amount of objects you need to loop through. However i always think "What if i worked in a large environment with thousands of objects" and then i begin to feel guilty that I may not be using the most efficient solution.


texts:
I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message:

  -1,311,750,183 rows inserted

Should I be concerned about the fact that it's negative?


texts: MongoDB start script - how does it choose configuration file?

texts:
I'm new to the MongoDB. I have the MongoDB server version 3.6.5 installed on my Xubuntu 14.04 VM, and I see it running (look at configuration file name):

ps -ef|grep mongodb
mongodb   79884      1  0 09:33 ?        00:00:44 /usr/bin/mongod --config /etc/mongod.conf

However, the start script /etc/init.d/mongodb contains the following line:

CONF=/etc/mongodb.conf

I have both mongodb.conf and mongod.conf files in the /etc directory. I don't see where in the starting script the mongodb.conf configuration file got replaced by the /etc/mongod.conf.

Can somebody please explain mechanics of this replacement?


texts: MongoDB gives certificate not trusted while openSSL says keys is OK

texts:
I have used let's encrypt to generate SSL certification. All the private key, chain and certificate is generated by `let's encrypt.

Now to use it in MongoDB I first merged privkey.pem and cert.pem into a file called mongo.pem:

cat /etc/dehydrated/certs/mongo.example.com/privkey.pem /etc/dehydrated/certs/mongo.example.com/cert.pem > /etc/ssl/mongo.pem

Now using openssl I converted ca.crt into ca.pem:

sudo touch /etc/ssl/ca.pem
sudo chmod 777 /etc/ssl/ca.pem
sudo truncate -s 0 /etc/ssl/ca.pem
sudo openssl x509 -in /etc/ssl/ca.crt -out /etc/ssl/ca.pem -outform PEM

Now a file is created called ca.pem in /etc/ssl path. Finally I append chain file content to the end of ca.pem:

sudo cat /etc/dehydrated/certs/mongo.example.com/chain.pem >> /etc/ssl/ca.pem

Now to verify the key I used openssl is below:

openssl verify -verbose -CAfile /etc/ssl/ca.pem /etc/ssl/mongo.pem

The output is:

/etc/ssl/mongo.pem: OK

It's good to note that I have done the same exact procedure on another server and it went all ok and MongoDB works as expected.

Now my MongoDB config is as below:

ssl:
    mode: preferSSL
    PEMKeyFile: /etc/ssl/mongo.pem
    CAFile: /etc/ssl/ca.pem

NOTE: My mongoDB is a single primary node in which replication is enabled (Use to capture changes on DB)

Now when I restart MongoDB it gives error below:

A member of a replica set enters RECOVERING state when it is not ready to accept reads. The RECOVERING state can occur during normal operation, and doesn’t necessarily reflect an error condition. Members in the RECOVERING state are eligible to vote in elections, but are not eligible to enter the PRIMARY state.

2018-05-15T18:26:00.171+0000 E NETWORK  [conn1] SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.171+0000 W NETWORK  [replexec-0] couldn't check isSelf (mongo:27017) socket exception [CONNECT_ERROR] for SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.171+0000 I NETWORK  [conn1] Error receiving request from client: SSLHandshakeFailed: SSLHandshakeFailed. Ending connection from 127.0.0.1:47454 (connection id: 1)
2018-05-15T18:26:00.171+0000 I NETWORK  [conn1] end connection 127.0.0.1:47454 (0 connections now open)
2018-05-15T18:26:00.171+0000 E NETWORK  [replexec-0] SSL peer certificate validation failed: certificate not trusted
2018-05-15T18:26:00.172+0000 I REPL     [replexec-0] This node is not a member of the config
2018-05-15T18:26:00.172+0000 I REPL     [replexec-0] transition to REMOVED from STARTUP

Modules cannot connect to MongoDB and reports:

OperationFailure: node is not in primary or recovering state

Now I do not have access to primary node and it cannot show my dbs. When I disable SSL, everything works and node gets back to primary state. Why is this happening, while I have done the exact same thing on a node in another datacenter?


texts:
I have a 1.4GB .gz backup file(which is 8GB unzipped) I have to import to the server but it isnt working whatever method I have tried.

1st I tried Workbench, after 2 days and still no progress I canceled, and after some research I found out console was the better option with big files.

On Console I tried importing directly from compressed .gz however that gave me ascii\0 error so I unzipped and tried uploading the file directly. This time it started very fast with some occasional spikes (like some rows gets 0.8 secs but the next row 11.76 secs). But this time it got stuck at the last second. I tried this step with 4 different OS and hardware setups.(Vista,7 server 08 and ubuntu also I tried with dedicated hardware also)

Since the file is big I cant just open it from notepad++ and I read that might corrupt the file also.

I cant get a new backup as the data came from outside source.

The database has 15 coloumns, myISAM engine.

As I can't seem to find anything else I would like some help as to what might be causing this issue.

Thank you very much.


texts:
how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g

here is the code that I try,it seems not working:

WITH
TABLE3 AS ( SELECT 2008 YEARS FROM dual WHERE 1=1
union all
select t3.YEARS+1 from TABLE3 t3
WHERE 1=1 AND t3.YEARS < 2011
)

select YEARS from TABLE3

expected result I want is :

2008
2009
2010
2011


texts:
We are running a relative small database which when download through bacpac comes as 80MB. But when it runs on Azure it expand to 2GB. In this database, we have couple of main table, but for this question I am focusing on this one table that hold all Customer Record who apply/register with us.
We get about 1000 entry in this database on average, 2000 when it is really busy. However the table is now 1.5M record old. Structure of table is:

CallID bigint Identity(1,1),
firstname varchar(50),
lastname varchar(50),
responsetime DateTime not null,
...
CompanyID bigint
EventID bigint

We have 80 columns in database, mainly containing address and other data. Now, in our reports on web, we need to show

How many new entry we get in last 14 days,
We list latest (today) entry in a Grid, so we query based on time and few data field.
We list/fiter our Grid based on given date range and/or event Id and/or companyid.

For this we over time we add 5-6 indexes, all non-clustered with 1, 2 field (like one is for companyid, other is eventid, third one is for ResponseTime) including other ID field

so most indexes are like

Create Index idxname on Leads (ResponseTime) include (companyId, eventID)

Now, the problem: We are not getting Good Performance on Azure SQL (s1) database tier. On Local Installation of Database our queries usually take <3 sec, but on Azure S1 it is taking 18-20 second. When we were on business Plan which is now retired (almost). it use to take < 2/3 second there as well.

We want to optimize the database, and certainly I believe that Indexes are not in good shape.
Most article/tutorial I found they says to create index, but not how to optimize the them. I spent last 8 hrs, trying to create new index and removing some old. but hardly can get a conclusive new index been create, because if I get performance benefit on one query it slows down other.

Now, is there any way, I can find out how to find best optimization of Indexes. I see azure/Sql server give missing index information, but it is asking to create 10 index on my table which already have 6 index, and most of them on single or two column and most of columns are repeated in multiple Suggested index.

Hope I make sense and might not get down vote for my question. Thanks.

EDIT: I found missing indexes using query from this article: https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx


texts: Replicate a database to one secondary replica, but not the other secondary replica using Availability Group

texts:
I have a 3 nodes cluster in an availability group.  There are two databases on the primary node.  One database I want to replicate to the 2 secondary nodes.  Another database I just want to replicate it to 1 secondary replica, not the other.  In the wizard, you have to select both nodes.  Is there a way to set it up using T-SQL so that one database only replicate to one secondary node, but not the 3rd node?

Thanks!


texts: GRANT CONTROL SERVER when user may be an entity owner

texts:
I have a login called user1. He only has the role of public. I want to grant CONTROL SERVER to him. So from sa I run this query

USE [master]

GRANT CONTROL SERVER TO [user1]

Which gives me this error

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

The only reason I can think of that this error is getting hit, is the entity owner condition. user1 may be an entity owner.

My question is, what can I do to give this CONTROL SERVER permission to user1?


texts: How to import big sql file in Phpmyadmin

texts:
I am having a big sql file( 16mb) and all i need is to upload it to the Phpmyadmin to work with it in my localhost. I'm a newbie here in Php/MySql and i want to know about the details regarding the work.So, please suggest me the best.

Cheers and waiting for response.


texts: Do you feel like DBA/DBD Is a "secure Field" still?

texts:
I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw together) coming down the line. Do you feel like DBA/DBD (database developer) is a secure field for a CS Graduate to go in. Also...do you feel that Databases in General are still as popular as they were say....5-10 years ago?

For reference I do have a job as a Developer right now, but Databases is something that's always interested me.

I know theirs a Career Overflow site, but I'd prefer to keep it here since it's Specifically about Database Development/Administration.

Thanks


texts: MySQL - Deduct from two columns with a remainder

texts:
I have the following table:

Id,  balance, otherBalance
=============================
1,     100,      50

I would like to create an update query that receives a parameter 'amount' and does the following:
deduct from both balances columns the entire amount. Starting with the 'balance' column first, and only when it is depleted, deduct the remainder from the 'otherBalance'.

Examples

Executing update(98), will result in:

Id,  balance, otherBalance
=============================
1,     2,      50

Executing update(102), will result in:

Id,  balance, otherBalance
=============================
1,     0,      48

Executing update(151), will result in no change since both balances are less than the amount to be deducted.