texts: Data Security for SQL Server 2012 Standard Edition

texts:
I just wanted to know what my options were for securing and encrypting data for SQL Server 2012 standard edition? We originally were going to use Transparent Data Encryption for restful encryption, but I have just seen that feature is available from Enterprise upwards.

The requirement is to secure the data is based wholly on this is user HR data. The DB itself will be used by a web app (with security around the app added as well). I'm looking now to see purely what we can do from a database side. The data itself should be locked down to HR personnel.

If the answer is purely, "You need to upgrade to Enterprise", that is fine.


texts: Alter table on live production databases

texts:
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?

I know the correct way is to backup everything schedule downtime and do then do the changes.

But... does any current database system support doing these things "on-line" without stopping anything? (maybe just delaying the queries that reference a column that is just being changed/deleted)

And what does it happen when I just do an ALTER TABLE... on a live running database?
Does everything stop when this happens?
Can data get corrupted?
etc.

Again, I'm mostly referring to Postgres or MySQL as these are what I encounter.

(And, yes, anytime I had to do this before I did it "the right way", backing things up, scheduling downtine etc. ...but I just want to know if it's possible to do this sort and things "quick and dirty" or if there is any DB system that actually has support for "quick, live and dirty" schema changes)

Someone just suggested Online Schema Change for MySQL from Facebook script (with a tutorial here and source here)... seems like a nice way to automate a the set of "hacky" ways to do it... has anyone ever used it in something resemblig production?


texts:
I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):

Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.

However, when I import the databases back, everything is written to ibdata1 file.
But, When I create a brand new DB via MySQL Client, and add a random table to it, the data is written to its own folder + table.ibd file.
Any idea what I could be doing wrong?
Few troubleshooting steps I tried:

Reboot
show variables like 'innodb_file_per_table';  (Value = ON)


texts: Finding the longest entries for a given column (nvarchar(100))

texts: I am trying to find the biggest / longest entries of a column which is of type nvarchar(100). I need to understand if I can maybe restrict that column to be only nvarchar(20) or something. Is there a way to do so? I know that I can't change the type to nvarchar(20) if I have any longer entries.


texts: Upload database records to AWS using TSQL?

texts: We have an AWS account that will be installing some software that ultimately needs a subset of our customers' data.  Our customer databases are either MS SQL Server or Sybase Anywhere.  Is there a way we can run TSQL SELECTs from our customer databases into databases on AWS?  If so, would this be resident on AWS pulling from the customer databases?  Or resident on our customer servers pushing to AWS?  If we were pulling, we would need our customers to provide us access to their IP addresses; is that insecure?


texts: IDERA suggests to alter procedures in tempdb

texts:
Idera analysis is suggesting I:

USE [tempdb]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

GO
ALTER PROC ##zDP_235302...

Is this really a good idea? Most of the "fixes" their diagnostic software suggests is spot on, but this kind of makes me take a step back. There is a long list of these procedures in tempdb they suggest altering.


texts: How to turn SELECT statement into a DELETE statement

texts:
I'm trying to turn this select statement into a delete statement and I'm struggling to not delete all the records in the table.  Here is the SQL select statement I'm using

SELECT  *
    FROM  message_cus_board mcb
    WHERE  mcb.board_id IN (
        SELECT  mb.board_id
            FROM  message_boards mb
            WHERE  mb.customer_id = :customerid
              AND  mb.universal_board = 'Y'
                          )
    ORDER BY  mcb.created_on DESC"

This queries all of the messages from the message_cus_board table and basically I want to delete all of the messages this would normally display except the last 15.  One thing I've tried is taking all of those message_id's and making an array of the most recent 15 then creating a delete statement with those id's like so ->

"DELETE FROM message_cus_board WHERE message_id NOT IN ($string_of_ids)"

The only issue is that this delete query gets rid of ALL other messages, not just the ones associated with the right customer.  I'm struggling to create a delete query that limits the delete to the right customer using similar logic to the select statement above.  I'm sure it's a fairly simple solution but it's currently eluding me.  Thanks for any help!


texts: Import poorly formatted CSV/JSON to SQL Server

texts:
I am trying to parse Android notification logs into a database. The columns are uneven because different notifications omit certain attributes. Essentially, it is a CSV file of key-value pairs. To put it another way, it is a really poorly formatted (& syntactically incorrect) JSON file. Maybe it would be easier to show you...

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

I have truncated them for the sake of brevity, but they do not line up toward the end of the rows. Some text values are quoted, some are not. All of the keys are quoted. I can use string functions to trim the extraneous characters once I get the columns sorted, but therein lies the rub: How do I sort the values into columns? I can do this in about 4 different programming languages, but I am just learning T-SQL.

How can I parse this to SQL Server (2016) in a single query? Is it even possible?

I could import it to a temporary table & then transfer it to the permanent one, but I would like to have one query to digest the info, since this will be repeated on a daily (maybe more) basis. The magic would be if I could declare a second-level delimiter, since all values are preceded by a colon. Declaring the colon as the delimiter breaks it the other way...arg(s)!

The data is all around ragged. Even the notifications from the same app may be drastically different. The way I would do it in PHP, C, JS, etc  would be to loop through the keys until I hit the ones I needed, grab the appropriate values following them (or the whole pair), then trim the extraneous chars. I just don't know how to do it in T-SQL & the online docs leave something to be desired.

The expected result would be something like this:

Package Name | Post Time | System Time | Offset | NotificationText
-----------------------------------------------------------------
automatic    | 1489795   | 786083      | -1400  | this is the note
gameservice  | 1489756   | 786090      | -1400  | this is the note
automatic    | 1489799   | 786045      | -1400  | this is the note

The big problem is that the info I need is scattered throughout the rows. I don't need much of it, but it is buried.


texts: Is it possible to connect to Oracle 9i data source in SSIS?

texts: We're doing a migration from Oracle 9i to SQL Server 2008. So I'm thinking of a way to move the data from Oracle and I thought about SSIS. I'm new to this Microsoft's technology and I would like to know whether it's possible to specify Oracle 9i as a data source, and do I need any further installations in case it's possible ?


texts: Dimensional model or Relational model for data warehouse?

texts:
I have a pretty trivial questions in data warehouse,

I know we use Dimensional data model to construct data marts and use Relational data model in staging area.

What data model technique we use to store data in central data warehouse?
Can we use both? Which is most commonly used?
We have RDBMS for relational database model, what is the database used for dimensional modelling?


texts:
I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level.

This is the plan that a normal insert generates (note the clustered index insert on VW_X.VW_CI which is the clustered index representing the view)

Is there any way I can track inserts on a view despite the fact that inserts don't really happen there, but at the base tables that create the view?


texts: Accessing the Oracle cloud with MySQL workbench

texts:
I want to connect the Oracle instances with MySQL workbench.
I do not know if it's possible or not.


texts: Merge/Intersection between table and dataset - How to achieve?

texts:
Consider the following table:

Id          Hash
----------- ----------------------------------
1           0x31F777F0804D301936411E3ECD760859
2           0xD64A593F3E9ACC972158D522A4289EA0

(Id is an identity column)

In to that table I want to merge the following dataset:

Hash
----------------------------------
0x31F777F0804D301936411E3ECD760859
0x31F777F0804D301936411E3ECD760859
0x0C5A65264F92A543E7AAA06375349C06

(Id is NOT present in the dataset)

The rules for the merge are:

If the hash does not exist in the table, insert it to the table;
If the hash does not exist in the dataset, delete it from the table;
If the hash does exist on both sides, and there are X instances in the table and Y instances in the source, then (Y-X) instances should be inserted in to the table.

The result of the merge should leave the table looking like this:

Id          Hash
----------- ----------------------------------
1           0x31F777F0804D301936411E3ECD760859
3           0x31F777F0804D301936411E3ECD760859
4           0x0C5A65264F92A543E7AAA06375349C06

What is the most efficient way to write a query to achieve this operation? FYI, other columns are omitted for brevity.


texts: Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

texts:
I had this issue long time ago, I found a workaround which suited me and forgot about it.

But now there's that question on SO so I'm willing to bring this problem up.

There's a view that joins few tables in a very straightforward way (orders + order lines).

When queried without a where clause, the view returns several million lines.
However, noone ever calls it like that. The usual query is

select * from that_nasty_view where order_number = 123456;

This returns about 10 records out of 5m.

An important thing: the view contains a window function, rank(), which is partitioned exactly by the field using which the view is always queried:

rank() over (partition by order_number order by detail_line_number)

Now, if this view is queried with literal parameters in the query string, exactly as shown above, it returns the rows instantly. The execution plan is fine:

Index seek on both tables using the indices on order_number (returns 10 rows).
Calculating windows over the returned tiny result.
Selecting.

However, when the view is called in a parametrized way, things get nasty:

Index scan on all tables ignoring indices. Returns 5m rows.
Huge join.
Calculating windows over all partitions (about 500k windows).
Filter to take 10 rows out of 5m.
Select

This happens in all cases when parameters are involved. It can be SSMS:

declare @order_number int = 123456;
select * from that_nasty_view where order_number = @order_number;

It can be an ODBC client, such as Excel:

select * from that_nasty_view where order_number = ?

Or it can be any other client that uses parameters and not sql concatenation.

If the window function is removed from the view, it runs perfectly quickly, regardless of whether or not it's queried with parameters.

My workaround was to remove the offending function and reapply it at a later stage.

But, what gives? Is it genuinely a bug in how SQL Server 2008 handles window functions?


texts: Get result from select exists, execute insert query based on the response of it

texts:
I have to insert unique value for a row but has to put check on 3 columns collectively ((unique value(col1 && col2 && col3) == true)

I found a solution here it is

query = select exists(select 1 from titles where "col1"='${col1}' AND "col2"='${col2}' AND "col3"='${col3}');

This query would return true or false based on the entries of the table

if(query == true)

execute insert into table values ('col1','col2','col3','col4','col5');

I am inserting multiple records from a json file using promises.

Is there any way to execute this in single sequelize.query().


texts: Index optimization on a view using a full outer join

texts:
Table structure:

Foo             FooChild                Bar
---             --------                ---
ID              ID                      ID
Date            FooID                   Date
GroupID         UserID                  UserID
Notes           Amount                  GroupID
                                        IsComplete

Foo has a unique index on Date+GroupID
FooChild has a FK to Foo, and a unique index on FooID+UserID, which includes Amount
Bar has a unique index on Date+UserID+GroupID, which includes IsComplete

Now I need to create a report showing the sum of all FooChild amounts alongside the count of complete Bars for any given date range.  The users also want to be able to see the stats per group or per user.  This would seem to be a great place to write a view:

create view vFooBar as
select f.Date, f.GroupID, fc.UserID, fc.Amount, b.IsComplete
from Foo f join FooChild fc on fc.FooID = f.ID
left join Bar b on f.Date = b.Date and f.GroupID = b.GroupID and fc.UserID = b.UserID
union
select b.Date, b.GroupID, b.UserID, x.Amount, b.IsComplete
from Bar b left join
    (select f.Date, f.GroupID, fc.UserID, fc.Amount
    from Foo f join FooChild fc on fc.FooID = f.ID) x
on x.Date = b.Date and x.GroupID = b.GroupID and x.UserID = b.UserID

(Here's why I wrote the view this way.)

Now I can easily write queries like this:

select UserID, sum(Amount) FooAmount, sum(cast(IsCompleted as int)) CompletedBars
from vFooBar
where Date between @fromDate and @toDate
group by UserID

But there's a snag here.  As soon as the date range starts getting relatively big, the execution plan goes all pear shaped.  It uses the date index on Foo, but instead of then using the FooID index on FooChild, it does a clustered index scan, then a hash match on FooID to join with the results from Foo.  And it does that twice in the overall plan; I'm guessing once for each aggregate.  And that really hurts.

I understand that using the index I created on FooChild may not be efficient, since the values of FooID for a given date might be discrete, though typically they are inserted in roughly the same order.

I could denormalize, and add Date and GroupID to the FooChild table, then index those columns, and I'm pretty sure that would improve performance a lot.  But it just doesn't feel right.

Any other ideas?


texts:
The data I'm working with is in a column ('transaction') of JSONB type, containing documents similar to this {"transactions":[{"id":"ABC123", ...}, {"id":"DEF456", ...}, {"id": "GHI789", ...}]}

I'm trying to get the distinct transaction id's but since the "id" field is part of a hash, inside an array, I'm struggling to get data out in a useful format.

I've got a solution that returns me something but it feels ungainly, and as a newcomer to Postgres, I feel like I'm not on the right track:

I'm using this from within a Python application, running sqlalchemy, but to get to grips with the data, I've constructed the following in the psql terminal:

WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders;

This returns me a rowset of JSON items extracted from the nested hashes. However, when trying to run this in sqlalchemy I get the following:

>>> query = sqlalchemy.text("WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders")
>>> rowset = engine.execute(query)
>>> rowset.fetchall()
[(u'ABC123',), (u'DEF456',), (u'GHI789',)]

... meaning I need to deconstruct further nested types from the sqlalhemy response. Which overall feels pretty cludged together.

Is there a better way to be querying this nested data?


texts: Strange use of Database Schema (Namespace)

texts:
Database schema helps grouping tables, or help identify multiple tables that have the same table name. Most of the schema usage that I have found so far are for grouping tables, isolating tables among the users (or talents), allow different permission based on the schema.

In my case, I have a predefined table definition, and I like my user to be able to duplicate this table multiple times in my application. Obviously, I can't have the user create the new tables with the same name. My original idea is to allow the user specify a table name suffix:

e.g.
[TableA]
[CustomName_TableA]
[OtherName_TableA]

However, this requires a bit of code change since the table name is hardcoded in the data access layer of the application.

What are the drawbacks, or potential issues if I create a new schema when a user duplicate the table, instead of dynamically generate a new table name?

e.g.
[dbo].[TableA]
[CustomName].[TableA]
[OtherName].[TableA]


texts:
I need to show some fields from another table in Oracle. Here is my query:

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT * FROM (SELECT CONCAT(TO_CHAR(abs(sysdate
    - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
       FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN =
        ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT =
        ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 12 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

The result I got:

[Err] ORA-00904: "KODE": invalid identifier

KODE come from query ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, and used for this query :

(SELECT *
        FROM (SELECT CONCAT(TO_CHAR(abs(sysdate -
    TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
            FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
WHERE ROWNUM = 1) AS MASA_KERJA

Do I miss something? Or could be using an alias in subquery wrong in the where clause in Oracle?

edited:
I have solved this problem, thanks for every one, here it is:

select
  e.nama as nama,
  e.nip  as nip,
    f.golongan as golongan,
    f.pangkat as pangkat_golongan,
    g.jabatan as jabatan_akhir,
    sub.tmt_jabatan as lama_menjabat,
    sub.kualifikasi as kualifikasi,
    sub.hari as hari_kerja
from
  simpeg_pegawai e,
  (
    select
      j.id_pegawai as id_pegawai,
            extract(year from (sysdate - j.tmt_jabatan) year to month) || ' Tahun ' ||
            extract(month from (sysdate - j.tmt_jabatan) year to month) || ' Bulan '
            tmt_jabatan,
            extract(year from (sysdate - j.tmt_jabatan) year to month) kualifikasi,
            trunc((((86400*(sysdate-j.tmt_jabatan))/60)/60)/24) as hari,
      row_number()
        over (
          partition by j.id_pegawai
          order by     j.tmt_jabatan desc
        ) as job_order
    from
      simpeg_jabatan j
  ) sub,simpeg_kode_golongan_pangkat f, simpeg_eselon_jabatan g

where
        sub.id_pegawai = e.id_pegawai
        and e.pangkat = f.id_golongan_pangkat
        and e.eselon_jabatan = g.id_eselon_jabatan
        and g.id_eselon2 = 2
        and g.status = 1
        and e.st_aktif=1
        and e.eselon2=2
        and e.pangkat >= 12
        and sub.job_order = 1
        and sub.kualifikasi > = 2
order by sub.hari desc


texts: Replication On Different Number of Columns

texts:
I have two schema with the same table name:
Example:schema_a.dbo.item having column 'a','b','c','d' where 'a' is primary key
and schema_b.dbo.item having column 'a','b','c','d','e','f','g' where 'a' is primary key.
schema_a.dbo.item table is my publisher database,i am using transnational replication
where schema_b.dbo.item table is my subscriber.Is it possible to still replicate data using transnational replication?
data type are same among common column between schema table.


texts:
We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups (We use Ola Hallengren's scripts).

Right now we are using robocopy to copy files over to a network share just in case our backups stored locally become corrupt, lost, or decide to take a nice long vacation. My question is, when we use the MIRROR TO option, is it any more/less resource intensive than just using robocopy, xcopy or a Powershell solution? What exactly happens when we use MIRROR TO in SQL Server? Looking for a bit of a 'nuts and bolts' answer, if possible.


texts: How do I uninstall only SSMS and Profiler for 2008R2?

texts:
I'm running SQL Server 2008R2 locally on my dev workstation, and I use SSMS and Profiler quite often.  I recently installed the July 2016 version of those tools, and I'd like to remove the 2008R2 versions in order to free-up some disk space.

What is the proper way to remove ONLY those management tools?  (I do not want to remove/break my core 2008R2 install)


texts: How to get actually allocated tablespace size by each postgres database?

texts: Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?


texts: Licensing of Oracle Standard One multiple homes

texts:
we I have a server with Oracle SE One ... I need to separate the test data from prod data as each one has it's own schema, I will do 1 of 2 which is both are OK for the dev team:

Create a new instance and move the test into it
Install a new home and then create the new instance into it then move the data, I like this option because I can test the Oracle patch on the test home before applying it on the prod ...

any thought on the license?
we already have the license, but do we need additional one if we do both or any one them?


texts: How to free the unused space for a table

texts:
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.

I use SQL Server 2014 Express edition with 10GB limit of database size (not a filegroup size, database size).

I crawled news, and inserted HTML into a table. Table's schema is:

Id bigint identity(1, 1) primary key,
Url varchar(250) not null,
OriginalHtml nvarchar(max),
...

Database ran out of size and I received insufficient disk space

Of course shrinking database and filegroup didn't help. DBCC SHRINKDATABASE didn't help. So I wrote a simple application to read each record, strip out some unwanted parts of the OriginalHtml like head section and aside and footer to keep the main body only and I now see this image when getting report of disk usage by top tables:

As I understand this picture the unused space is now like 50 percent of total size. That is, now I have 5GBs unused space. But I can't reclaim it.
Rebuilding indexes didn't help. The truncateonly option won't help because as I understood no record is deleted, only the size of each record is reduced.

I'm stuck at this point. Please help, what should I do?

Clustered Index is on column Id.

This is the result of EXECUTE sys.sp_spaceused @objname = N'dbo.Articles', @updateusage = 'true';

name        rows     reserved     data        index_size   unused
----------- -------- ------------ ----------- ------------ -----------
Articles    112258   8079784 KB   5199840 KB  13360 KB     2866584 KB


texts:
I'm trying to run a query on a table to see how many unique users have a usage record in the system at a given point. I've been working with the following query, but I've yet to see a proper result.

SELECT count(distinct usageUser), divisionName
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName;

Currently the query returns 18, for each division in the joined table. Without the GROUP BY clause I get the same number of records.

EDIT: I ran the query again, with suggestions from a comment. By removing the group by and count clause, I get this this (too big to post). This data is very poorly formatted, unfortunately it's inherited and fairly large.

It is not possible for these users to have used every lab like it's listed.

This was originally posted to server fault, and was migrated to StackOverFlow.  I was then pointed to here saying this might actually be the most appropriate place for it.


texts: Postgresql behaviour between diferent stages

texts:
I have three Postgresql instances (Postgresql clusters) in three diferent stages.
The instances are the sames with the same datas and the same parameters.
But i do not have the same behaviour between one stage and the two others.

For example, a query is executed in 5 minutes in one stage and in 3 seconds in the two others.
I checked the execution plan and one index was not used causing a slow full scan. After some manipulations like vacuum analyze, running the query with enable_seq_scan to false (and reset to true after some runnings) and after the week-end, the index was used and this query was executed in 12 seconds. It is still slower.

In general, the behaviour is slower than the two others.
The systems are the sames (rhel 7, ram, cpu) and postgresql.conf (shared_buffers, etc) are the sames too.
The servers are dedicated for the databases.

Have you got some ideas about the issue ?
What are the parameters i have to check and compare. I surely missed something.

Rhel 7
Postgresql 9.5.12

EDIT
Hereunder some parameters with diferent values between the two stages:

some parameters from the stage which does not work well:

work_mem = 60MB
maintenance_work_mem = 14GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
max_wal_size = 13GB
wal_keep_segments = 1000

Some parameter from the stage working well:

work_mem = 50MB
maintenance_work_mem = 15GB
checkpoint_timeout = 1h
checkpoint_completion_target = 0.9
max_wal_size = 512GB
wal_keep_segments = 150


texts:
I perform daily full backups of all databases. Differential backups are performed every hour.

The issue I am having is that around the same time each morning, a few hours after the full backup, the differential backup sizes increase from 20MB to 250MB.  How do I track down what is causing a 200MB jump in the backup size?

This mostly causes problems in backup retention since the total size then balloons dramatically throughout the day.


texts: Proof that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF?

texts:
"A key is simple if it consists of only one attribute". Prove that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF. Your proof should be general, e.g., it should not assume that R has a fixed number of, say two or three, attributes.

Solution:

Consider an FD X -> Y that holds in R. Since R is in 3NF, either

X is a superkey or
Y is a member of a key.

In the second case, since every key in R is simple, Y is itself a key, which implies that X is a superkey.
Therefore, X -> Y does not violate BCNF in either case, which implies that R is in BCNF.

I understand everything except the final part; Y being a key implies X being a superkey. Can somebody elaborate on that?


texts: Tracking changes to a table via the update/insert cmds executed against it daily

texts: We have an oracle database back end for our SAP ECC system.  In it we have a table with over a billion records.  We would like extract this table to our SAP BW, which happens to be on MSSQL Server.  The table we are trying to extract has no change pointer or column for which to track the changes made against it.  Given its volume we are unable to kill and fill it every day.  We have looked at Oracle GoldenGate but do not want to spend that kind of money.  Has anyone else done this kind of work before?


texts:
We have had a mongodb running fine for a few weeks and all of sudden it has gone down.

We are unable to start it back up as it is complaining that the dbpath (/data/db/) does not exist. I've tried routing through the documentation and have seen others suggest using the dbpath flag, however still no luck.

We are starting it with sudo service mongodb start and the config file dbpath config points to a directory with our db in.

Any advice would be greatly appreciated.


texts: QUERY - pivot multiple columns, variable number of rows

texts:
I have a table that looks like this:

RECIPE     VERSION_ID     INGREDIENT    PERCENTAGE
4000       100            Ing_1          23,0
4000       100            Ing_100         0,1
4000       200            Ing_1          20,0
4000       200            Ing_100         0,7
4000       300            Ing_1          22,3
4000       300            Ing_100         0,9
4001       900            Ing_1           8,3
4001       900            Ing_100        72,4
4001       901            Ing_1           9,3
4001       901            Ing_100        70,5
5012       871            Ing_1          45,1
5012       871            Ing_100         0,9
5012       877            Ing_1          47,2
5012       877            Ing_100         0,8
5012       879            Ing_1          46,6
5012       879            Ing_100         0,9
5012       880            Ing_1          43,6
5012       880            Ing_100         1,2

There are 100 ingredients per recipe/version. I'd like to display the data from this table like this:

RECIPE     INGREDIENT_Vxxx     PERCENTAGE_Vxxx     INGREDIENT_Vyyy     INGREDIENT_Vyyy (ETC)
4000       Ing_1               23,0                Ing_1               20,0
4000       Ing_100             0,1                 Ing_100              0,7

Because in different versions of recipes, ingredients could be removed or added, I'd like to display both ingredient and percentage per version per recipe. There's also the difficulty that different recipes have different number of versions.

I'm not even sure if this is possible at all or where to begin. Maybe with the PIVOT function?

Could anyone please point me in the right direction?


texts: Configure SSMS intellisense / web browser settings to accept UNC directory path inputs

texts:
I would like to navigate to open a directory path from an in-line commented UNC path. SSMS integrates with Windows Explorer and Explorer allows UNC navigation from the address bar.

Indeed, when in the integrated browser, it is possible to navigate among UNC paths. HOWEVER, the click-thru functionality provided for http:/ prefaced URLs in the text editor of SSMS does not seem to exist for UNC paths. The Web Browser section of Options / Environment appears to open Internet Explorer options and the Text Editor / Plain Text / General section has the "Enable single-click URL navigation option", but does not appear to allow definition of what is detected as a URL.

Screenshots linked below

(added sample code below)

use [master]
go

-- http://www.google.com
-- \\servername\unc\BackupFolder\

select 'This query needs to be uploaded to the URL' Header
union
select 'and also saved for archiving at the UNC path'
union
select 'so is it possible to clickthrough to both?'


texts: When you are hired as a DBA in a new shop, what are the important tools that you would use for establishing baselines and implementing benchmarks for 50+ instances? You advice would be highly appreciated.


texts: How to apply query to select of another query, PostgreSQL 9.6

texts:
I have a query, which is working well:

    WITH geom_1 AS
    (
      SELECT ST_Transform(ST_SetSRID(ST_MakePoint(VAL_1, VAL_2), 4326),32652)
    ), point_3d AS
       (
         SELECT  ST_MakePoint(ST_X(st_transform),ST_Y(st_transform), VAL_3) FROM geom_1
        ), static_points AS
           (
             SELECT geom FROM eco.static_points WHERE name='Korea'
           )
   SELECT ST_3DDistance(st_makepoint, geom) FROM point_3d, static_points;

It's just needed to provide VAL_1,VAL_2,VAL_3.
I want take them from another query:

WITH param AS (
    SELECT longitude, latitude, altitude
    FROM eco.tracks
    WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND altitude IS NOT NULL
    )

So, input is 3 column, and output is column with distance.
How can I do it?

UPD: Looks like I did

   WITH
    coordinates AS (
    SELECT id, longitude as lon, latitude as lat, altitude as alt FROM eco.tracks WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND altitude IS NOT NULL
    ),
    point_3d AS (
    SELECT  ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt)  FROM coordinates
    ),
    static_points AS (
    SELECT geom FROM eco.static_points WHERE name='Korea'
    ),
    distance_table AS(
    SELECT  id, ST_3DDistance(ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt), geom) as distance FROM coordinates, static_points
    )

And updating third one

UPDATE eco.tracks tr
SET distance_1 = dt.distance
FROM distance_table as dt
WHERE tr.id = dt.id


texts: innodb_force_recovery when InnoDB corruption

texts:
When I start mysqld (in /etc/init.d), it failed with

InnoDB: corruption in the InnoDB tablespace.

What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but corruption error message is the same. I already have a full dump.


texts:
I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage.

The server will be setup for a CRM environment of 100 people.

Server specs:

MySQL version 5.7.28-0ubuntu0.18.04.4-log
16GB of RAM

Changes to mysqld.conf:

key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size   = 8

# My changes

innodb_buffer_pool_instances = 12
innodb_buffer_pool_size     = 12G
innodb_flush_method         = O_DIRECT
innodb_log_file_size        = 1G
innodb_page_cleaners        = 4
innodb_purge_threads        = 4
max_connections             = 151
open_files_limit            = 112274
table_open_cache            = 56137

log_output                  = FILE
default-storage-engine      = INNODB
character-set-server        = utf8
innodb_lock_wait_timeout    = 300
read_buffer_size            = 64K

# Recommendations [mysqltuner]

long_query_time         = 10
query_cache_size        = 0
query_cache_type        = 0
query_cache_limit       = 2M
join_buffer_size        = 512K
table_definition_cache  = 28468
log_bin                 = /var/log/mysql/mysql-bin.log

What mysqltuner says in InnoDB Metrics:

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 12.0G/22.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (66.6666666666667 %): 4.0G * 2/12.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 96 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (5004553901 hits/ 5004951598 total)
[OK] InnoDB Write log efficiency: 96.96% (8250637 hits/ 8509152 total)
[OK] InnoDB log waits: 0.00% (0 waits / 258515 writes)

Can you help me with this? Should I change anything else in the conf file? Should I upgrade the hardware?

EDIT:

GLOBAL STATUS: https://pastebin.com/1kbHTGkL

GLOBAL VARIABLES: https://pastebin.com/KUrBh9wr

FULL PROCESSLIST: https://pastebin.com/39GZdeWB

MySQLTuner report: https://pastebin.com/Khdgf7GP

EDIT 2:

I'm using SSD Storage

ulimit: https://pastebin.com/aUAtjnUN

iostat: https://pastebin.com/kjr9GaLB


texts: Specifying the IP address to be used when connecting to an instance

texts:
I have an application that was compiled with a hard-coded connection string (using the server's IP address '192.168.40.201' and not the server name) to an SQL Server.

A backup of the database has been restored on a stand-alone machine with no working network adapter and I would wish to be able to connect to this machine's SQL server using the IP address specified in the connection string of the application.

Is there any way to specify that the IP address to be used for this particular instance is '192.168.40.201'?


texts: Ideal frequency to gather table statistics in Oracle for database of 1GB in size

texts:
I want to know what should be the ideal Frequency for Analyzing tables in a schema.
Database size 1GB.

Currently the frequency is set to 7 days, it takes around 40 mins to complete. Data volume is not huge.

Please let me know how can I optimize it?

What all options and to what values they should be set for DBMS_STATS.GATHER_TABLE_STATS.


texts: Is there a way to use Oracle XE as an easy-rollback "proxy" to regular Oracle

texts:
For development, we're currently connecting to shared dev databases that are way too big for XE (some are 100s of GB). Is there a way to set it up so that we can have a local XE on each developer's machine that acts as a proxy to the shared server for reads, but stores both DDL and DML changes in the local XE schema, so that it can easily be reset by the developer without requiring intervention from a DBA (after initial set up)?

It may be that Oracle has another product that does what I want, rather than XE? This is a big (non tech) company you've all heard of, so (AFAIK) we have a "whole site + whole suite" license.

Any thoughts? Am I dreaming?


texts: AVG using specific condition for each row

texts:
I have a Quotation table with QuotationID, ClientID field.
A QuotationDetail table with QuotationID, ProductID
A Sales table with Year, ProductID, ClientID and Quantity.

I'm trying to create a view with the following columns:
QuotationID | Client Name | Product Name | Quantity2015 | Quantity2016

I am able to join the quotations table with clients and products but can't figure out how to SUM Quantity values for each view row filtering by YEAR (2015 and 2016).

PS: I did not specify but I have the Clients and Products tables with the fields I mentioned.


texts: how to update a column from another database?

texts:
I would like to update a field that has been delete from the backup database.
how can I update the field from an other database


texts: MySQL reporting version mismatch due to picking up previous installation

texts:
To solve a mysqldump "Workbench was unable to get mysqldump version. Please verify the log for more information.", I opted for the first solution at

https://stackoverflow.com/a/44851129/175241

But now workbench does not throw a error, so I check the error log.

14:32:30 [ERR][SqlEditorSchemaTree]: Exception querying metadata from mysql.proc, will fallback to SHOW PROCEDURE: Exception: Table 'mysql.proc' doesn't exist
14:32:31 [INF][wb_admin_control.py:query_server_info:714]: WbAdminControl.connect_sql(): The server version stored in the server instance profile was "5.7.25-0ubuntu0.18.04.2". Changed to the version reported by the server: "8.0.15"

select version(), WB Server Status, mysqld, and mysqldump returns 8.0.15 . So before I start to further debugging, I was wondering if anybody knows what else may have let to my server instance profiled be populated with the wrong MySQL version. I had previously uninstalled this version and upgrade to MySQL 8. When I do which on mysqld and mysqldump, I only get 1 instance of each.

Here is my config:

MySQL Workbench Community (GPL) for Linux/Unix version 6.3.8 CE build 1228 (64 bit)
Configuration Directory: /home/ras-pc/.mysql/workbench
Data Directory: /usr/share/mysql-workbench
Cairo Version: 1.15.10
OS: Linux 4.15.0-46-generic
CPU: 4x Intel(R) Core(TM) i5-4590T CPU @ 2.00GHz (1970.580MHz) - 7.70GiB RAM
Distribution: Ubuntu 18.04.1 LTS

Checking my installation logs on 5 of March, I found

Mar 05 08:58:09 raspc systemd[1]: Starting MySQL Community Server...^M
Mar 05 08:58:14 raspc systemd[1]: ^[[0;1;39m^[[0;1;31m^[[0;1;39mmysql.service: Main process exited, code=exited, status=1/FAILURE^[[0m^M
Mar 05 08:58:14 raspc systemd[1]: ^[[0;1;39m^[[0;1;31m^[[0;1;39mmysql.service: Failed with result 'exit-code'.^[[0m^M
Mar 05 08:58:14 raspc systemd[1]: ^[[0;1;31m^[[0;1;39m^[[0;1;31mFailed to start MySQL Community Server.^[[0m^M
dpkg: error processing package mysql-community-server (--configure):^M
 installed mysql-community-server package post-installation script subprocess returned error exit status 1^M
dpkg: dependency problems prevent configuration of mysql-server:^M
 mysql-server depends on mysql-community-server (= 8.0.15-1ubuntu18.04); however:^M
  Package mysql-community-server is not configured yet.^M
^M
dpkg: error processing package mysql-server (--configure):^M
 dependency problems - leaving unconfigured^M
Errors were encountered while processing:^M
 mysql-community-server^M
 mysql-server^M
Log ended: 2019-03-05  08:58:15

and on 6th of Mar, the server seems to be running properly.

Setting up linux-libc-dev:amd64 (4.15.0-46.49) ...^M
Setting up mysql-community-server (8.0.15-1ubuntu18.04) ...^M
Setting up mysql-server (8.0.15-1ubuntu18.04) ...^M
Log ended: 2019-03-06  06:36:00

So I presume everything is fine from the installation standpoint. I have been using the database every work day since then.


texts:
Need help regarding backup validations for MySQL, what is the best way / easiest way to validate Mysql backups.

we are using mysqldump backup for taking backup's and tar the backup file at the end, could not find any solution on my research.

Any help from this community, Thanks in advance.


texts:
Let me first tell you my current environment in which there is one windows cluster on which there is sql cluster with two nodes, i.e, A and B in it. Of which, one A is active and B is passive.
So, what I want is to use my passive node, i.e, B for some other application's database and make it work like active node for this application and A which will act as passive in this case.
Using this I can utilize my one server which is free at a time.

The point is that can we do that?


texts: On-premesis SQL Server setup for MFA against Azure AD

texts:
I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition).  I believe the SSMS is simple enough.  Under the "Login" tab, I have chosen "Active Directory - Universal with MFA support" and have my user name (like "john.doe@example.com").

Under "Connection Properties," I put in a specific database and my Azure AD tenant ID (guid):

When I try to log in, I get a popup from Microsoft, which I assume is the SSO login:

However, when I put in my password, I get error 18456, State 1:

I assume this is because I need to add the user on the server side.  However, I have not been able to find how to do that for an on-premesis SQL Server (there are plenty of guides for Azure SQL).  I am using Standard Edition 2017.  What do I need to do to complete the setup?


texts: optimize SELECT query with CASE statements and JOINs

texts:
I'm creating a social network, and I need a query to fetch the posts for a user's feed. I have the following (simplified) tables:
CREATE TABLE users (
  user_id bigint NOT NULL (primary key),
  username character varying(32)
);

CREATE TABLE posts (
  post_id bigint NOT NULL (primary key),
  repost_id bigint,
  text text,
  media jsonb,
  timestamp timestamp with time zone DEFAULT NOW() NOT NULL,
  user_id bigint
);

CREATE TABLE followers (
  follower_id bigint NOT NULL (primary key),
  user_id bigint NOT NULL,
  following_user_id bigint NOT NULL
);

Users kan follow other users, and their posts will appear on the feed. Users kan repost another post, I want to include these reposts in the feed.
I have the following query (for now). This query works, but is has a lot of CASE statements and joins, and I don't know if this query is the best way to go. I'm a postgresql beginner and I want to know if somebody has some tips to perfect this query.
SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
LEFT JOIN
  posts b
  ON a.repost_id = b.post_id
JOIN
  users c
  ON a.user_id = c.user_id
LEFT JOIN
  users d
  ON b.user_id = d.user_id
WHERE
  a.user_id IN (SELECT following_user_id FROM followers WHERE user_id = $1)
  AND
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10

Explanation:
I select all posts from the table posts (a) where the user_id is contained in the results of the query to select all the following_user_id's of a user from the table followers (subquery in WHERE). I also do a LEFT JOIN on the table posts (b) to select the information from the reposted post. In the SELECT with the CASE statements, I check if the repost_id is NULL. If it is null, I need the post information from (a), otherwise, I need the post information from (b). The same thing with the usernames. username from (c) is the username from the post itself, the username from (d) is the username of the reposted post (if this is a reposted post), or NULL if it's an original post itself.
A second option is to copy the row op the original post when someone reposts a post, but I want to know if my initial option is OK to?


texts: What do you disable, uninstall, remove from your OLTP SQL Server to keep it as clean as possible?

texts:
I'm looking for answers on all the services you disable, anything that you disable, uninstall, etc.  The goal of this topic/question is have a running checklist of things that do NOT need to be on a SQL Server for an OLTP-type system.

I've not been able to find a definitive list on this topic, just various things here and there.

I wanted this list for my own edification as well as something the community could refer to as needed.

My thought is to update the list as folks make suggestions in the original post.

I know that some of the topics that'll come up can be considered subjective, but either way, I'd like you to explain your reasoning for why you make the suggested modification or disablement.  Not all of these are blatantly obvious.


texts:
I'm a SQLite and SQL beginner so please bear with me. I'm trying my hand a creating a simple podcast index database. Here's the schema for my EPISODE, CAST_MEMBER, and EPISODE_CAST tables.

CREATE TABLE EPISODE
(
    id                  INTEGER PRIMARY KEY,
    show_number         INTEGER UNIQUE NOT NULL,
    air_date            DATE UNIQUE NOT NULL,
    runtime             VARCHAR(255) NOT NULL
);

CREATE TABLE CAST_MEMBER
(
    id          INTEGER PRIMARY KEY,
    person_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE EPISODE_CAST
(
    episode_id      INTEGER NOT NULL,
    cast_member_id  INTEGER NOT NULL,
    PRIMARY KEY(episode_id, cast_member_id),
    FOREIGN KEY(episode_id) REFERENCES EPISODE(id),
    FOREIGN KEY(cast_member_id) REFERENCES CAST_MEMBER(id)
);

I'm trying to find a quick way to populate the EPISODE_CAST table. I know what all the relationships should be, I just don't want to have to write a million insert statements. For example, one cast member has been on every episode of the show so I naturally want 550 rows added to the table

episode_id | cast_member_id
===========================
1          | 1
2          | 1
3          | 1

… and so on.

So here's the query that I've written

INSERT INTO EPISODE_CAST (episode_id, cast_member_id) VALUES
   ((SELECT id FROM EPISODE WHERE show_number BETWEEN 1 AND 550),
   (SELECT id FROM CAST_MEMBER WHERE person_name='John Smith'));

But I'm only ever getting one row inserted. (The 1|1 row.) So I'm obviously missing something here. A loop of some sort…

Once I have that one cast member in there, I'd also like to do something similar with the other cast members. But for them I'll be using an IN clause with the set of all the episodes that person was in (most are non-contiguous).

Again, sorry for my naïvety. But thanks for any help.


texts:
I have one table now with million rows..

Table-1

Name, Age, Sex, Dept
Tom   25   M    Finance
.
.
.

Now, The "Finance" is reptitive...Currently, there is only one dept "Finance"..

Is it good to normalize the data to a different table..

Finance Table

Id Name
1  Finance

and refer Finance-id in the Table-1


texts: When duplicate date values exist in a column, change the value of the oldest

texts:
I have a query that returns records with duplicate Month and Year in a date field.  I need to identify the records with duplicate Month and Year, then identify the oldest based upon the full date and change the value of a column that displays the month based upon the date column to that month - 1.

Background:  The query grabs the last 12 bills for a customer.   In a few instances, the Date (actually the read date - which is typically the last day of the month) reflects when a meter was read.  The only time there could be read dates with the same month year in that 12 month period is when a read date (the last day of the month) falls on a non business day, then it pushes to the next month.  I'm looking for the usage in the month the meter was read for the last 12 months.

Example, in the query results below the highlighted month should read Feb as it is the oldest of the duplicate Mar 2017 results.


texts: Import all csv files into a table oracle

texts:
Trying to import all the files into a table but I receive the following error.

#!/bin/bash
. ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1
sqlldr USERID=user/pass@mydb DATA=/data/SDP/Import/*.csv \
    CONTROL=/home/path/test.ctl LOG=/home/path/test.log

Error Message

 SQL*Loader-500: Unable to open file  (/path/path/Import/*.csv)
 SQL*Loader-553: file not found
 SQL*Loader-509: System error: No such file or directory
 SQL*Loader-2026: the load was aborted because SQL Loader cannot  continue.

Please help.


texts: selecting complete rows grouped by one column based on the maximum of another

texts:
I'm trying to find a query selecting all columns of my table, where the result set contains only distinct values of a certain column. The row being selected for a specific distinct value should be the one with the maximum value of another column (within the group of rows having that value for the former column).

I tried

SELECT column1, column2, MAX(column3) FROM table GROUP BY column1;

which doesn't work because in the results column2 and MAX(column3) are not from the same row. How is this achieved in mysql?

Example

For a table like

CREATE TABLE table (column1 VARCHAR(10), column2 VARCHAR(10), column3 INT);

and data like

INSERT INTO table VALUES ("a", "x", 1);
INSERT INTO table VALUES ("a", "y", 2);
INSERT INTO table VALUES ("a", "z", 3);
INSERT INTO table VALUES ("b", "x", 1);
INSERT INTO table VALUES ("b", "y", 2);

I want to get the rows ("a", "z", 3) and ("b", "y", 2). With the above query I get ("a", "x", 3) and ("b", "x", 2) i.e. MAX(column3) and the rest of the result do not come from the same original row in my data. I see why that is not possible with aggregation functions in general but I can't think of another simple way to achieve it.


texts: We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly appreciate your help. Thank you very much.


texts: Oracle Triggers - Before Update and Before Insert - Separate or Combined?

texts:
I'm running Oracle DB 9i.

I have a table with various constraints to ensure data integrity.  In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that necessary data goes into the table, in some cases allowing the application layer to omit the information from their queries, and in some cases forcing that it be present.

The actions that I need to do on my BEFORE INSERT are different than my BEFORE UPDATE.

My question is this:

Can I have one common trigger that uses IF / THEN, or should I make separate triggers?

For example:

-- Scenario 1
   CREATE OR REPLACE TRIGGER   my_trg
              BEFORE INSERT OR UPDATE ON my_table
                 FOR EACH ROW
   BEGIN
      IF UPDATING THEN
         IF :NEW.my_col IS NULL THEN
            raise_application_error(...);
         END IF;
         IF :NEW.my_col2 IS NULL THEN
            :NEW.my_col2 := (...);
         END IF;
      END IF;
      IF INSERTING THEN
         IF :NEW.my_col3 IS NULL THEN
            SELECT my_seq.NEXTVAL INTO :NEW.my_col3 FROM DUAL;
         END IF;
         IF :NEW.my_col4 IS NULL THEN
            (...)
         END IF;
      END IF;
   END;
/

Or:

-- Scenario 2
   CREATE OR REPLACE TRIGGER   my_trg1
              BEFORE INSERT ON my_table
                 FOR EACH ROW
   BEGIN
      IF :NEW.my_col3 IS NULL THEN
         SELECT my_seq.NEXTVAL INTO :NEW.my_col3 FROM DUAL;
      END IF;
      IF :NEW.my_col4 IS NULL THEN
         (...)
      END IF;
   END;
/

CREATE OR REPLACE TRIGGER   my_trg2
              BEFORE UPDATE ON my_table
                 FOR EACH ROW
   BEGIN
      IF :NEW.my_col IS NULL THEN
         raise_application_error(...);
      END IF;
      IF :NEW.my_col2 IS NULL THEN
         :NEW.my_col2 := (...);
      END IF;
   END;
/

Currently, these two triggers are combined.  Should I separate them out?


texts:
I'm trying to get query execution time, but I also want to hide the query output. I just want the elapsed time - no output.
Example
DECLARE @Start datetime
DECLARE @End datetime

SELECT @StartTimeWA=GETDATE()

SELECT
       [id]
      ,[database_id]
      ,[proc_name]
      ,[exec_t] from
  [DB].[dbo].[STAT]

SELECT @End=GETDATE()

SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

At the moment, I get query output and at the bottom my duration, which is the only thing I want as far as the output goes. I haven't been able to do this and wondering whether anyone else has come across similar problems? This is something I want to do in T-SQL not Management Studio or anything like that.
I'm trying to monitor the time it takes to run the select statement and report back to a server. I have an external monitor server which will run that every minute and get the time back (duration it took) which I will use over time to trend / baseline. As the current query spits out the select results and my duration it skews it, and my monitor server gets confused. I just wanted the duration column. I will also be doing this for inserts, which will be straightforward as it won't need to perform a select.
I'm trying to do this purely in T-SQL. I don't want to use DMVs as I want to get the time it takes (snapshot) when I run a query and check whether this changes over time when the server goes through the various levels of load as this will give me a good idea as to whether query execution time changes.


texts: Can an article in Sql Server replication be based upon a query?

texts: I am looking to replicate the results of a query, is that possible or do I need to rep the tables themselves?


texts:
I have a field  in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two different things:

SELECT x hour, ... FROM table WHERE ... GROUP BY x ...

x = SUBSTR(CAST(time AS CHAR(14)), 9, 2)
x = MOD(FLOOR(time / 10000), 100)

I built a quick benchmark (did 1000 querys with each version) and both of them seem to be equally quick so I'm really interested in what database designers would advise me to do?


texts: Report Server tempdb's chunk data table is growing very large on daily basis

texts: In SQL Server 2005, reportservertempdb chunk data table is growing too much in gbs. Need to know why? We are truncating it on a daily basis, still it grows too much. Database is in simple mode. Need to clarify why is it growing so much.


texts: Mysql fulltext search is not returning any rows which is having dot(.)

texts:
Here it is not fetching any rows from the table .

My table structure

tb_business

|  business_id  |    title    |
|-----------------------------|
|     1         |   abcd.efg  |
|     2         |   vwxyz     |

Here 'title' is the full text column.

query 1:

select * from  tb_business where MATCH(title) AGAINST("vwxy*" IN BOOLEAN MODE)

result :

|  business_id  |    title    |
|-----------------------------|
|     2         |   vwxyz     |

Here everything is good.

query 2:

 select * from  tb_business where MATCH(title) AGAINST("abcd*" IN BOOLEAN MODE)

But here it is not fetching any rows. showing blank result. I expected the result like

|  business_id  |    title    |
|-----------------------------|
|     1         |   abcd.efg  |

I am using Mysql version 5.6.38

How do i solve this ?


texts: SQL Cluster, AlwaysOn AG, or Something Else?

texts:
I really needs some help with Setting up SQL servers.  We are re-configuring our network and have no clue how to resolve our SQL issue.  My Network Admin. wants an "Active/Active" with shared storage (SAN) solution.  The reason for this is load balancing, redundancy, and auto-fail over.  I do not believe this can be done or should be done.  Please help me figure this out.  I have listed some info. below on our setup.  Thank you

We will most likely use three servers, possible using VMs
We have a SAN with 1GB networking
Solution needs to have auto-fail over and the secondary node's database needs to be synchronous
If possible with a solution to have mixed hardware (Intel, AMD)
All servers and equipment located in one location
We will be using either SQL Server 2012 R2 or 2014


texts: MySQL inserts/deletes slowing down after a while after Start/Reboot

texts:
The Setup here is:
OS: Ubuntu 12.04 (1 Core and 1.75 GB of RAMO)
MySQL Version: 5.5

There are about 8 to 9 tables in the database. On initially starting mysql the queries are fast at around 0.05 - 0.10 seconds, however after sometime the INSERTS and DELETES slow  down to around 1 - 2 seconds, however the SELECTS are still around 0.05 - 0.10 seconds.

Then after rebooting the system the situation reverts back to how it was in the beginning of the previous paragraph. Everything is fast, but then INSERTS and DELETES slow down tremendously after a while.

Note: I'm just doing simple inserts and deletes on very few records, and the database starts with empty tables in the beginning.

Any insight?

Edit: Create Table of 1 of the Tables

| FeedUps |
CREATE TABLE `FeedUps` (
  `post_id` int(10) unsigned DEFAULT NULL,
  `liker` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `post_id` (`post_id`,`liker`),
  KEY `liker` (`liker`),
  CONSTRAINT `FeedUps_ibfk_1` FOREIGN KEY (`post_id`)
      REFERENCES `Feed` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FeedUps_ibfk_2` FOREIGN KEY (`liker`)
      REFERENCES `Users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Quickest Method To Invalidate Statistics

texts:
I'm doing some testing on sync vs async auto statistics updates. I'd like to quickly invalidate all statistic objects (headers, density vectors, and histograms) to ensure that next time the statistic is used that it will be updated.

I'm trying to simulate an auto update of statistics, not an auto creation.

Ideally I don't want to change the row count so I've dismissed INSERT/DELETE operations.  Ideally, I don't want to change any data values either, I have considered using UPDATE statements but I'm thinking this could take too long on some of my larger tables.

I had looked at UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT but I don't think this is what I'm after. I was hoping there was maybe a trace flag or undocumented command that would invalidate statistics.

Is there an quick, efficient way to do what I want to achieve that I haven't considered?

I'm testing on SQL Server 2016.


texts: Add AWS EC2 instance to existing Availability Group

texts:
I currently have an AG with 2 replicas both running SQL Server 2016. These are both located on premise. I was looking to add a third node using a AWS EC2 instance and asynchronous replication.

Networking issues aside, is this an acceptable approach?

The only resources I can find on the internet appear to use distributed AGs but that would mean having a second EC2 instance in AWS


texts: different log file for master and beta mysql

texts:
Hi i am facing a problem while shipping my database log

I have three database server dev, beta and master
and i maintain log files for dev on github
and every log file has a version given to it like (001 next would be 002 and so on)
and then i would merge my code on github from dev branch to beta branch , clone the code on beta deploy server run this log files on beta while deploying its build(then while deploying it checks for last updated .sql file and start deploying from next file), test it and if all ok
then again
i would merge my code on github from beta branch to master branch, clone the code on master deploy server run this log files on master while deploying its build, test it and if all ok
that would be my final release
sample log file

          20140926.sql
          --add a stored procedure
          --update employee where id=10;
          --alter table .etc

but now i am facing a problem in this
i have a table called as employee
and on this i need to have different changes
like example

          20140926.sql
          --update employee set name="beta" where id=10;--this should go in beta database only BUT NOT IN MASTER
          --update employee set name="master" where id=10;--this should go in MASTER database only BUT NOT IN BETA
          --update sometable set something='somevalue' where nothing='nothing';--but his update is common for both BETA and MASTER branch

but if now i commit it on dev and merge to beta and deploy on beta this the changes which should go ONLY on MASTER are going on BETA
and this is totally screwing my whole exsisting logic


texts: Risks involved in upgrading to SQL Server 2008 R2

texts:
We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same.

The 2005 SQL Servers are all SP3 and SP4, running on Windows Server 2003 (whose support has already ended, but we have exception of extension for one more year), but where required we might go with a server OS upgrade as well.

These servers include replication (transactional), log shipping, reporting services, and an integration server running SSIS packages.

My question here is not how, rather I would like to know the risks involved or any pre-checks that can be done before planning this upgrade?

Also, will an in-place upgrade be a better plan than side-by-side for this migration/upgrade?


texts: Liquibase / PostgreSQL problem: ? operator becomes ??

texts:
I've been using Liquibase for a few months in a pre-production context, and just ran into this mystery issue.
It appears that the source code using the JSON ? operator in Postgres is getting created in the database as ?? which is not valid.
For example, this source code
WHERE f.filter_object::JSONB ? 'patient_status';
appears like this when I view the SQL in pgAdmin
 WHERE f.filter_object::JSONB ?? 'patient_status';
Is there some setting that may be controlling this translation?
Our changesets use the sqlFile tag, if that matters.


texts: PostgreSQL long transaction on a table performance

texts:
We have a long processing pipeline running on PostgreSQL 9.0.

Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation?

We're talking in the order of 5 Bulk Inserts and 30 Updates, on one table, querying from other tables. Table rows are in the range of 1-5 million.

We're planning to upgrade to PostgreSQL 9.4 soon. Are there any difference across versions?


texts: Is there any difference between a predicate using a literal value or a parameter value when that value will always be the same?

texts:
I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?

(This is in the context of an ad-hoc query. The datatype of UserStatus is int)

...
AND UserStatus = 1
...

or

DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...

(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)


texts: Mysql: Insert performance INNODB vs MYISAM

texts:
I am inserting into a simple small table with 5 attributes and 1000 rows.

I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds.
I changed the engine to MYISAM, then the inserts are faster. it is taking 0.001 - 0.003.

What is the problem. innodb_flush_log_trx_commit = 1 by default. I was this setting as it is.
Here are my innodb setting.

innodb_log_buffer_size : 1MB
innodb_log_file_size   : 5MB
innodb_buffer_pool_size: 8MB
innodb_flush_log_trx_commit = 1

mysql> desc table ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
| count | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

VARIABLES

mysql> show variables like 'innodb%' ;
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 2097152                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |

+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)

I could not able to figure out what went wrong with this.
    Thanks in advance.
    Regards,
    UDAY


texts:
I've tried to restore a MySQL database using incremental binlogs and mysqlbinlog tool but I received the following message:

ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

BINLOG_FORMAT parameter in my database is set to ROW, but is there a way to restore binglog with this parameter in ROW?


texts: Inserting into subtype table values from supertype table in oracle

texts:
I had created two object types and tables based on that types in oracle:

create or replace type TEMPLOYE as object(
num_emp integer,
nom_emp varchar2(40),
prenom_emp varchar2(50),
adresse_emp varchar2(200),
tel_emp varchar2(15)
) not final;
/
create or replace type TMEDECIN under TEmploye (
num_med integer,
specialite Varchar2(30)
);
/
CREATE TABLE EMPLOYEES OF TEMPLOYE;
CREATE TABLE MEDECINS OF TMEDECIN;

and I had inserted some values into EMPLOYEES like :

INSERT INTO EMPLOYEES VALUES (
TEMPLOYE (10,'BOUCHEMLA','Elias','6, hai sidi serhane -Khemis El Khechna-Boumerdes',024873549));

Finally I need to insert a record into MEDECINS table with num_med=10 and specialite='Cardiologue' and the others attributes from the parent table (EMPLOYEES), how to do that using select * statement or a reference?


texts: Tiny query used to be sub-second speed, now takes 60+ seconds

texts:
Ok, I've got a monitoring process that runs the following query to make sure the databases are backing up regularly.  This process used to take 1-2 seconds for connection, query execution, and everything.  Now the process is timeing out and if I run the query in SMSS it takes over a minute.

SELECT D.name AS [database_name], D.recovery_model, BS1.last_backup, BS1.last_duration
FROM sys.databases D
LEFT JOIN (
    SELECT BS.[database_name],
    DATEDIFF(HH,MAX(BS.backup_finish_date), GETDATE()) AS last_backup,
    DATEDIFF(MI,MAX(BS.backup_start_date),MAX(BS.backup_finish_date)) AS last_duration
    FROM msdb.dbo.backupset BS
    WHERE BS.type = 'L'
    GROUP BY BS.[database_name]
) BS1 ON D.name = BS1.[database_name]
ORDER BY D.[name]

Now the sub-select by itself still takes less than a second and returns about 20 rows (the msdb.dbo.backupset table has ~500K rows).  The sys.databases table has about only about 30 rows.  So I'm lost as to why this query now takes a minute to run.

The problem started after the C: drive was full for a few minutes so I thought maybe an index for corrupted. So I've rebuilt all the indexes (including PK) on the msdb.dbo.backupset table.  I can't find the sys.databases table so I'm guessing it's a "virtual table" or something?  I looked at the estimated execution plan but that stuff is mostly over my head.

Any suggestions?


texts:
I'm facing a issue while importing the dump in my system, i'm getting the error as

  " Error: syntax error at or near ""   LOCK TABLESDB_NAME` WRITE;"

and exiting with "out of memory"

We had taken back-up from Suse linux and trying to import in  Cent OS 6.3.

Any suggestion to fix this issue.

Thanks in Advance,


texts: Functions causing Serial plan.What to use instead?

texts:
I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan.
For example :

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK)
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO

In this stored procedure,i have a function in the where condition

 WHERE ---
AND r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber))

dbo.GetPlant_Id(@LNumber) is a function

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPlant_Id]
(
    @LNumber varchar(50)
)
RETURNS VARCHAR(25)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM tblReport r WHERE (SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)) = @LNumber HAVING COUNT(r.LNumber) > 1)
    BEGIN
        RETURN cast('Duplicate Incident number found :' + @LNumber as int)
    END

    RETURN
        (SELECT TOP 1 AgencyOri FROM tblReport r WHERE SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber)
END

GO

How do i avoid using functions.?I tried to convert to inline function,but since it is a big function,i cannot put it as an inline function.
To make my question clear,

I was asking how to avoid using function like i showed in the question or is there any way i can change it to improve the performance.?


texts: How to get all non deleted messages from one user

texts:
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:

CREATE TABLE IF NOT EXISTS `default_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `sender_user_id` int(11) NOT NULL,
  `reply_to_message_id` int(11) NOT NULL,
  `thread_root_message_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

And this is the DATA for this table:

INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES
(1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
(2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
(3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
(4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);

Now for the second table this is the SQL:

CREATE TABLE IF NOT EXISTS `default_recipient` (
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `read` int(11) NOT NULL DEFAULT '0',
  `recipient_read_date` datetime DEFAULT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the DATA is this one:

INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
 (1, 2, 1, NULL, 0),
 (2, 1, 1, NULL, 1),
 (3, 1, 1, NULL, 1),
 (4, 2, 1, NULL, 1);

Now I have this query for get all messages for user_id=x where deleted columns in both tables default_recipient and default_messages is not equal to 1 but it's not working because I get two results instead of one when user_id at table recipient or sender_user_id at table messages takes value equal 2:

SELECT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 2 OR rcp.user_id = 2
        AND msg.deleted = 0 AND rcp.deleted = 0
        ORDER BY msg.date DESC

What I'm doing wrong?

EDIT: The right question here was: how to get all messages for one user "X" let says where rcp.deleted = 0 OR msg.deleted = 0


texts:
I am new to mysql and I have a query in regards to mysql replication.

Currently, I have a master database that replicates to four slave servers at the moment. I would need to set up a few replication servers(as slave) but these will replicate only a few databases. How would approach this issue?

Should I be using something like this on slave /etc/my.cnf?

replicate-ignore-db=db_name

Can I set something on slave configuration stating exactly which databases and tables to be replicated?

Should the changes happen only on the slave configuration or on master as well? The reason being this master server currently replicates whole lot of databases to other slaves which will remain as it is.


texts:
Is there an equivalent to MySQL SHOW DATABASES statement?

Is it possible to find databases in a cluster? i.e. databases present on the network on some other system?

Could I analyze the files present on an Oracle installation to find the same?

Given complete access credentials to an Oracle system how would you go about enumerating all the databases that exist?


texts:
I found the following ERD design:

Which trying to capture as much information about employee leave (i.e sick, travel, etc).
What am trying to understand is what does the columns:

Table : Employee_Annual_Leave

year_number // Why is this set as the PK? What does it mean by
year_number der_leave_cumulative_days_taken // where could it be
possible be derived from?
der_leave_days_remaining // and how does
these two column work in real scenario?

PS: Yes i have read the "Note" box written above it. But i still dont get it.

Could anyone explain what it means?


texts: 6th normal form, recomposition query, efficient implementation

texts:
I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the language tutorial D. I understand how to convert all this logic into SQL Server. But at the end of the he shows how well this can be implemented in current database management systems, and then I see this part I need to implement:

Recomposition query: can be done but likely to perform horribly.
Might be preferable to store PERS_INFO as a single table under the
covers, so that the tables resulting from decomposition can be
implemented as mappings to that. But current technology doesn’t give
clean separation of physical storage from  logical design. Perhaps
something for the next generation of software engineers to grapple
with?

It suggest storing PERS_INFO as a single table under the covers, but what does that really mean? How would I implement that in SQL Server?


texts: What are the minimum permissions needed to allow Edit Top 200 in SQL Server Management Studio?


texts: Placing Transaction Log on Separate Volume [solid state]?

texts:
Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute write operations with far greater speed sequentially as opposed to randomly. This is due to the little needle inside the drive which has to move a much shorter distance when writing sequential blocks of data, as opposed to random writes.

(Sorry for the naive interpretation. Just trying to make sense of what I've read.)

With this in mind… It occurs to me that solid state drives don't have little needles and platters and stuff moving around inside of them. If my database and transaction log are both located on a single RAID 5 of eight solid state drives, is there really any upside to moving the transaction log onto its own separate volume? If the supposed efficiency boost is based upon the premise of sequential writes reducing the distance that needles move and platters spin, and a solid state drive has none of these moving parts, what do I gain by isolating the log?


texts:
I have 4 MySQL tables

1. companies - {id*, company}
2. employees - {id*, fname, lname, id_company*}
3. technologies - {id*, technology}
4. company_technologies - {id_technology*, id_company*}

Field is indexed if it has '*'.

Companies can use multiple technologies and have multiple employees.

What I'm trying to do is write a query that would select all employees and their companies that use ANY of given technologies. The problem is that I have duplicates when I try to do something like this:

SELECT employees.*, companies.* FROM companies LEFT JOIN employees on companies.id = employees.id_company LEFT JOIN company_technologies ON companies.id = company_technologies.id_company WHERE company_technologies.id_technology IN(1,2,3)

If, for example, a company is using technology 1 and 2 - it will appear twice along with all people.

If I add 'GROUP BY companies.id' condition at the end - I only have one employee for each company, whereas I need all of them.

Tables 1, 2 and 4 have several million records, all tables are InnoDB.


texts: Postgres - fast way to enforce AND across multiple rows

texts:
I've got a 4 column table with ~25M rows:

tweetifai=> \d core_tagusage;
                                 Table "public.core_tagusage"
  Column  |         Type          |                         Modifiers
----------+-----------------------+------------------------------------------------------------
 id       | integer               | not null default nextval('core_tagusage_id_seq'::regclass)
 tag      | character varying(30) | not null
 score    | double precision      | not null
 tweet_id | character varying(16) | not null
Indexes:
    "core_tagusage_pkey" PRIMARY KEY, btree (id)
    "core_tagusage_tag_3ed0f993a18dd430_uniq" UNIQUE CONSTRAINT, btree (tag, tweet_id)
    "core_tagusage_cea90699" btree (tweet_id)
    "core_tagusage_score_772bb061400905c6_uniq" btree (score)
    "core_tagusage_tag_443351eb_uniq" btree (tag)
    "core_tagusage_tweet_id_11461293c1e23f80_like" btree (tweet_id varchar_pattern_ops)
Foreign-key constraints:
    "core_tagusage_tweet_id_11461293c1e23f80_fk_core_imagetweet_id" FOREIGN KEY (tweet_id) REFERENCES core_imagetweet(id) DEFERRABLE INITIALLY DEFERRED

There is a many-to-many relationship between tweet_id and tag.

Given a few tags (eg. "dog", "grass", "frisbee") I'd like to retrieve all tweet_ids  which match all tags and sort by score. Here's my query:

SELECT *
FROM (SELECT
        COUNT(ort.tweet_id) AS numtags,
        ort.tweet_id,
        MIN(ort.score)      AS minscore
      FROM (SELECT
              tg.tweet_id AS tweet_id,
              tg.tag      AS tag,
              tg.score    AS score
            FROM core_tagusage AS tg
            WHERE tg.tag = 'dog'
                  OR tg.tag = 'grass'
                  OR tg.tag = 'frisbee'
           ) AS ort
      GROUP BY ort.tweet_id) AS ct
WHERE numtags = 3
ORDER BY minscore DESC
LIMIT 200;

Even without the ORDER BY this is remarkably slow (~10-20 seconds). Here's what I get from EXPLAIN ANALYZE:

Limit  (cost=0.56..22720.77 rows=200 width=33) (actual time=24177.117..24177.117 rows=0 loops=1)
  ->  GroupAggregate  (cost=0.56..1327311.85 rows=11685 width=25) (actual time=24177.115..24177.115 rows=0 loops=1)
        Group Key: tg.tweet_id
        Filter: (count(tg.tweet_id) = 3)
        Rows Removed by Filter: 493764
        ->  Index Scan using core_tagusage_cea90699 on core_tagusage tg  (cost=0.56..1321691.90 rows=547389 width=25) (actual time=0.206..23322.853 rows=501654 loops=1)
              Filter: (((tag)::text = 'dog'::text) OR ((tag)::text = 'grass'::text) OR ((tag)::text = 'frisbee'::text))
              Rows Removed by Filter: 24309855
Planning time: 0.237 ms
Execution time: 24177.166 ms

Is there a different query I could write to achieve the same results?

edit:

EXPLAIN ANALYZE SELECT
              tg.tweet_id AS tweet_id,
              tg.tag      AS tag,
              tg.score    AS score
            FROM core_tagusage AS tg
            WHERE tg.tag = 'dog'
                  OR tg.tag = 'grass'
                  OR tg.tag = 'tree';

Bitmap Heap Scan on core_tagusage tg  (cost=295.51..44454.72 rows=13794 width=32) (actual time=8.253..105.051 rows=104691 loops=1)
  Recheck Cond: (((tag)::text = 'dog'::text) OR ((tag)::text = 'grass'::text) OR ((tag)::text = 'tree'::text))
  Heap Blocks: exact=1133
  ->  BitmapOr  (cost=295.51..295.51 rows=13797 width=0) (actual time=8.111..8.111 rows=0 loops=1)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=3.238..3.238 rows=38385 loops=1)
              Index Cond: ((tag)::text = 'dog'::text)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=2.217..2.217 rows=30477 loops=1)
              Index Cond: ((tag)::text = 'grass'::text)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=2.649..2.649 rows=35864 loops=1)
              Index Cond: ((tag)::text = 'tree'::text)
Planning time: 0.093 ms
Execution time: 187.032 ms


texts: Executing a Stored Procedure from a Trigger

texts:
I set up an AFTER INSERT trigger on a table that executes a stored procedure.
For some reason, after an insert occurs on the table, the stored procedure doesn't run and prevents the insertion.

When I try to execute the stored procedure from new T-SQL, it works just fine.

I tried adding try catch to the trigger, but no error is caught.

I tried troubleshooting using SQL Server Profiler and I found out that the stored procedure starts running after the insert, but stops in the middle of it, although it says the statment is complete.

Any ideas what could have caused this, and how to fix it?


texts: Is there a workaround for lack of support of cascade triggers with MySQL?

texts:
As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a workaround for this.
I'm currently using triggers to do something like this:
CREATE TRIGGER `Set_field1_on_update` BEFORE UPDATE ON `mytable`
FOR EACH ROW BEGIN
    SET NEW.field1 =  CONCAT(NEW.myforeignkey,'whatever');
END

Which works fine when myforeignkey is changed directly on mytable but not when it is updated with ON UPDATE CASCADE via its referenced table.
Is there anyway to achieve the above functionality (using something else than triggers if necessary) on cascaded foreign key actions?


texts:
Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.

When and why is it useful to have negative (or rather signed) keys indexing a table?


texts: Oracle 11gR2 (11.2.0.4) RMAN cold backup | Media recovery

texts:
I'm planning to setup a test server with production data. The intended actions are, making a cold backup & restoring it in the Test server.

The cold backup routine is

run
{
shutdown immediate;
startup mount;
backup database;
}

As I am restoring a cold backup, should be bothered about losing some data? The database is on Archive logging mode. Should I do a recover media as part of the restore process?


texts: mongoose in node js won't connect to cluster when VPN enabled!

texts:
I'm trying to connect my node js app to MongoDB using mongoose. It works well and it connects without any problem. but when I want to connect MongoDB when my VPN is enabled it throws this error:

net:27017' => [ServerDescription] },
stale: false,
compatible: true,
compatibilityError: null,
logicalSessionTimeoutMinutes: null,
heartbeatFrequencyMS: 10000,
localThresholdMS: 15,
commonWireVersion: null } }

I was using this connection String:

mongodb+srv://parham:@testcluster.na0z1.mongodb.net/users?retryWrites=true&w=majority

note: password is obscured
then I contacted MongoDB support and they gave me this connection String for an older node version:

mongodb://parham:@testcluster-shard-00-00.na0z1.mongodb.net:27017,testcluster-shard-00-01.na0z1.mongodb.net:27017,testcluster-shard-00-02.na0z1.mongodb.net:27017/users?ssl=true&replicaSet=atlas-57dilh-shard-0&authSource=admin&retryWrites=true&w=majority

I don't know why but it connected the first time I used it but it never connected again.

and it's somehow related to the first answer on stack overflow.
stack overflow question


texts: Scripting Local Policy Perform Volume Maintenance Tasks

texts:
I am working on an automated SQL 2012 install process on Windows 2012 R2 and have everything complete except for one task.  My process launches secpol.msc but I am still required to drill in and manually assign access to Perform Volume Maintenance Tasks to the SQL service account.  Once I close the window the process takes over and installs and configures everything else without user intervention.

I have done extensive internet searching and the closest I have found is this article from 5 years ago by Kendra Little: http://www.littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights/.

Is there a means via scripting to set Local Security Policies?

Thank You,
Scott


texts: SQL - referential integrity - Foreign key and Check constraint

texts:
Please let me know if below two declarations are same.

create table R1
( a INT PRIMARY KEY,
  b INT references K(w));

Note: K is a table with single attribute w as primary key

create table R1
( a INT PRIMARY KEY,
  b INT CHECK (b in (SELECT w FROM K));


texts: How do I set a database back to 'Restoring...'?

texts:
Sorry if this is a noob question. I have transaction log shipping set up for a failover scenario. All the databases are in 'Restoring...' state until I do restore database x with recovery. At this time I disable log shipping.

Once I have resolved the situation and I want to enable log shipping and carry on again. Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out?


texts: How do completely empty columns in a large table affect performance?

texts:
I have 400 million rows in a Postgres db, and the table has 18 columns:

id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without time zone,
h integer,
i timestamp without time zone,
j integer,
k character varying(32),
l integer,
m smallint,
n smallint,
o character varying(36),
p character varying(100),
q character varying(100)

Columns e, k, and n are all NULL, they do not store any values at all and are completely useless at this point. They were part of the original design, but never removed.

Edit - most of the other columns are non-NULL.

Questions:

How can I calculate the impact this has on storage?
Is it equal to size of column * # of rows ?
Will dropping these empty columns noticeably improve performance for this table?
Would the page cache be able to fit more rows?


texts: SORT(DISTINCT) :Is it because of Kitchen sink queries?

texts:
One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and based on the user search criteria, it passes parameters to the query.

I looked into the execution plan and I see sort operations. One of the sort operation is SORT(DISTINCT). I checked the query and I found some order by in the query. Removing order by didn't help, and I dont have a DISTINCT in the query. On checking the properties of the SORT(DISTINCT) it is not showing the field on which it is doing sort. I have attached the screenshot of that.

After doing some research I learned that OR in where conditions can generate SORT operations as the MERGE required some kind of sort, and I do have OR in where conditions which looks like:

where (column1 = @param1) OR (column1 IS NULL) OR (@param1 IS NULL)

Someone suggested me in an earlier post that, this are "Kitchen sink" queries and it will cause performance issues.

Is there anyone who had to deal with similar situation? Any solutions?


texts:
I have a badly designed table and to top that number of columns is increasing (~500)because each time a new machine is discovered.(eg: router,adapter windows server etc)..it will store the output of commands which we after discovering that machine which is handled by back-end.

  For Example: We discover  a docker running in a machine. We will get the containers and image details....saving those as Blueprints and its properties in the table with appropriate columns;

table contains 1 millions records. We frequently do joins because this particular table is our main table. As to normalize the database now is not feasible, Can anybody give a workaround or any suggestions(like partition or sharding).
Thanxin advance ..Comment if you need more details


texts: Using max result as a value for a predicate in SQL

texts:
Say I want to find all the employees that earn more than all of the employees of Google, is it possible to use the result from max as if it was a single value and not a table like so?

select person-name
from Works
where salary > ( select max(salary)
        from Works
        where company-name == "Google" )

(Works has the columns person-name, salary and company-name)


texts: Joining Products and Orders so that every product is listed along with the quantity a specific Buyer has ordered

texts:
Table A is a list of "products" and table B is a list of "orders" referencing products (using product_id), along with buyers informations (buyer_name ecc) and the quantity of each product buyed.

An order has many products and a single buyer, so that when a buyer issues an order, a row with the same buyer_name and order_no is inserted into table B for each product ordered along with the desired quantity.

Which are the two fastest queries to join "products" and "orders" so that for every "products" row I have also the quantity that a specified buyer has ordered:

in a single order (order_no is specified)
in all his orders (sum quantity in every order issued by the buyer)

To be efficient, all the "orders" rows not belonging to the specified buyer (and not having the specified order_no for the first query) should be excluded before the join, but every row from "products" should be returned, even if the product has not been ordered from the buyer (quantity should be 0 in this case).

UPDATE (from comments):

Current query:

SELECT p.*, o.buyer_name, coalesce(o.quantity,0)
  FROM products p
         LEFT JOIN (SELECT *
                      FROM orders
                     WHERE buyer_name='Joe'
                      AND order_no=123
                   ) AS o USING(product_id)

I'm not having a performance issue now, and am not looking to tightly tune this specific query, but to ensure I'm writing queries efficiently in general. Similarly, current DBMS is PostgreSQL 9.4.4 - but looking for generic advice that would apply to most SQL variants.


texts: Excel grab data from MS SQL Server with WHERE clause based on cell contents

texts:
There's a bunch of questions for similar things, but I've been unable to find anything that fits.

We have three columns:

Name   |    ID    |    Cost
Thingy       3          25
Other        11         5

Only ID is manually entered. Name and cost on that row are then fetched automatically from an MSSQL database.

I've been looking at using Microsoft Query data source with ? in the WHERE clause to then add parameters based on cell contents but firstly, the parameters button is greyed out and secondly, even if it worked, it seems like I'd need to manually create queries for every single row with cell reference hardcoded in rather than one cell query that could be replicated down the name and cost columns.

Any guidance would be hugely appreciated.