texts: Tab color for registered servers in Management Studio

texts:
SSMS allows us to use custom color for each connection.

Does anyone know where this information is saved? I'd like to keep the color setup but I keep losing it.

edit

When I connect from object explorer, I can set the color. However, this seems to be different from the colors that I set in registered servers, and this I keep losing. (Indeed, I am on a roaming profile.)

I use registered servers panel only when I do multi server operation and try to be much more careful when I have that panel open, so that's why I also want to be able to use colors associated with the object explorer.

Thanks


texts: Do i need streaming replication slots as many of replicas in postgres?

texts:
Correct me if I am wrong but I’m thinking that if I have 4 different read only replica servers I need 4 streaming replication slots, one for each of them?
Or all of them can you use one?


texts:
I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema:

Adding a new column at the end of the table: "Table is changed"
Removing a column from table: "Table is changed"
Adding a column in between existing columns or just changing their order: "The recreation of the table was started"

(I did the translations myself, they maybe inaccurate but similar).

Step No. 3 takes forever during publishing process. I wonder why and what exactly is going on. I suspect the table structures are recreated entirely. I would not want this, I woul`d basically not care very much about the columns orders in the different target systems. Is there any way to prevent this time consuming process?

Edit:

Meanwhile I found that many people are struggeling with the same problem. SSDT does not have any option to ignore the column order of the target tables. That makes it re-arrenge all the data as described above. This is time consuming and entirely useless, but there is no choice if you want to use SSDT.

See & please upvote here:

Visual Studio Uservoice Item

Microsoft Connect Item

MSDN Forum Discussion


texts: MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

texts:
I have the following table definition:

CREATE TABLE `invite` (
  `id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_i_e` (`email`)
)

My goal with the not null constraint on id is to make sure there are no null values as the primary key, and to ultimately fail to insert.

However, not specifying the id on insert works just fine:

INSERT INTO invite (`email`) VALUES ('e@mail.com');

There is an 1364 warning specifying Field 'id' doesn't have a default value, but the insert succeeds just fine.

It looks like MySQL is using the not null constraint to turn this into a non null (blank) value at insert.

If I actually specify null as a value of id, the insert fails as expected.

Without having a trigger to stop empty strings being inserted, how can I stop this scenario from happening, i.e force a non empty string value to be specified?


texts: DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

texts:
I have the following table in PostgreSQL 9.4:

CREATE TABLE dpa(
  id serial NOT NULL,
  currency_id integer,
  amount numeric(14,3),
  date timestamp without time zone,
  plat_id integer,
  pl_id integer,
  p_id integer,
  CONSTRAINT dpa_pkey PRIMARY KEY (id),
)

and settings:

work_mem = 128MB
table_size = 16 MB

And index:

CREATE INDEX idx1
  ON dpa
  USING btree
  (plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST, amount)

The table consists of approximately 242K rows. I don't have NOT NULL constraints on the column, but they are actually NOT NULL.

Now, I'm measuring performance of the queries:

I

SELECT plat_id, p_id, pl_id, player_account player_account
FROM(
    SELECT plat_id, p_id, pl_id,
    COALESCE(amount, 0) player_account,
    ROW_NUMBER() OVER (PARTITION BY plat_id, p_id, pl_id, currency_id
                       ORDER BY date DESC NULLS LAST) rn
    FROM dpa
) sub WHERE rn = 1;

Analyzed plan:

Subquery Scan on sub  (cost=0.42..25484.16 rows=1214 width=44) (actual time=0.044..296.810 rows=215274 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 27556
  ->  WindowAgg  (cost=0.42..22448.79 rows=242830 width=28) (actual time=0.043..255.690 rows=242830 loops=1)
        ->  Index Only Scan using idx1 on dpa  (cost=0.42..16378.04 rows=242830 width=28) (actual time=0.037..91.576 rows=242830 loops=1)"
              Heap Fetches: 242830

II

SELECT DISTINCT ON(plat_id, p_id, pl_id, currency_id)
       plat_id, p_id, pl_id, currency_id, amount
FROM dpa
ORDER BY plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST

Analyzed plan:

Unique  (cost=0.42..18794.73 rows=82273 width=28) (actual time=0.017..128.277 rows=215274 loops=1)
  ->  Index Only Scan using idx1 on dpa  (cost=0.42..16366.43 rows=242830 width=28) (actual time=0.016..72.110 rows=242830 loops=1)
        Heap Fetches: 242830

As can be seen, the second query is faster than the first one. But when I execute this queries in PGAdmin I got the following average statistics:

The query with ROW_NUMBER()(the first): 4999 ms

The query with DISTINCT ON(the second): 5654 ms

I understand that bandwith/latency overhead on such large result set is significant. All queries produce 215274 rows.

QUESTION: Why does it take more time to receive all rows in the second case than in the first one, although the planner shows that the second plan is more optimal?


texts: MySQL Workbench - "deploying" schema into a real database

texts:
a noob here, finding my way around. So, how can I "transfer, deploy or whatever the right term is" the database tables, fields, etc. that I have created with Workbench into a real database?

The dB I have is currently on a local WAMP installation but I also have it on a real web server.

Many thanks,
Alex

EDIT: I wish to use MySQL Workbench to "draw" database tables, columns and all the other info that is needed for a database to function properly and then transfer this onto my server. Meaning, I wish to avoid having to use clunky phpMyAdmin to create database for my application but instead create it on my PC with Workbench and then transfer all this onto real production server. Thanks!


texts: A performance Issue when querying a big table using a PK/clustered index

texts:
To simplify the situation, I will only consider one large table...
On a nightly basis a store will send all the new and changed data for one day for a large table to the head office. (this portion is good)
Also, the store sends a summary of that table for the last 30 days to the head office for that large table.

At the head office,
The new and changed data is updated in the large table  (no problem here).
The summary of the last 30 that is received and is uploaded into a table.  It is then compared to a query that summarizes the data at the head office of this very large table (that contains all stores) for that same store.<-- this is where the problem is.  This is done to be able to make sure that the store's data matches the head office data for that store (we get a warning if it doesn't match for which they need to take action)

The problem is the the summary query takes too much time... I'm looking to change the way we compare the store table with the host table in a more efficient way.

I tried the indexed view and the results were great but the fact that they have too many limitations, it makes it practically impossible to implement it on a large scale (to all software owners, cash registers, Stores and Head offices) due to different structures and different versions of our software.

I've been trying to think of different ways I can insure that the data of a table  (for at least the last 30 days) for a store matches the head office but I feel like I'm turning in circles... So I'm looking for ideas to help me look at this differently.

Limitations: We use SQL Express at the stores, and usually standard at head offices.  There's no direct connection between both databases (the data is transferred through files).

Any help is appreciated.
thank you

Added more info:
Structure of the table (I know is not ideal, it's what I inherited):
Date, Store, terminal, transNum, lineNum, Qty, Amount + 194 MORE COLUMNS.
The PK and clustered index is: Date, Store, terminal, transNum, lineNum

The query to summarize is simple:

Select Date, Store, sum(Qty) as Qty, sum(Amount) as Amt
from MyHugeTable
where date between '2017-07-22' and '2017-08-22'
and store = '1234'
group by Date, Store;


texts:
I found lots of ShrinkD, ghostcleanuptask, and BTree Split/Shrink in my SQL Server logs.

At the time of these logged events, the performance counters showed that lazy writes were very slow, the log flush rate was high, and write/sec was close to 30000. Disk IO was also very high.

I'm sure that the server traffic was normal and there was no abnormal data file growth at the time, but the log file was twice as large as usual. I used the dump_log() function to get statistics on logs:

My configuration:

Windows Server 2008 sp2
SQL Server 2014
Database target_recovery_time = 60 seconds
Database auto shrink and index auto shrink = false
Log backup hourly
Physical disk: pcie ssd 3.0T with 70% used

I recently:

Cleaned up useless indexes
Reorganized fragmented indexes
Increased the automatic archiving data operation about 500w daily backup and delete

Archiving was at 4:00-5:00 very day, I suspect that the ghost cleanup is related to my physical backup deletion and archiving. The logfile was double than the same time yesterday.

My Question:

What is the "shrinkd" in SQL Server logs?
Is it shrink database?
I can't find any information about it on the Internet.If the AUTO_SHRINK set off and there is not skrink job,how did it be triggered?
Thanks.


texts: How do I see where postgres privileges are coming from for a role?

texts:
Im using google cloudsql but this is a more generic postgres question.
I have a role (that is already created and managed by cloudsql) called cloudsqliamserviceaccount.
The cloudsqliamserviceaccount role is not a member of any other roles.
Whenever I create a new postgres user (through GCP console or gcloud) they are added to this role. I CANNOT remove them from the role because it will break the cloudsql IAM integration.
The cloudsqliamserviceaccount role has very sweeping permissions. It can connect to and do CRUD on all DBs. But I'm not sure how to see its full list of permissions.
In short I have this role "cloudsqliamserviceaccount". How do I see what it has access to? I gets CRUD automatically for every new database I create, but its not a member of any other role. Where is this permission coming from and how do I see that?


texts: Timeout in SSIS package running DBCC CHECKDB

texts:
I have created an SSIS package to run DBCC CHECKDB on all the databases on the server and send error messages by email.

But for several of the databases, I get an exception running my script:

  Timeout expired.
  The timeout period elapsed prior to completion of the operation or the server is not responding.

The only timeout I can find in the project/package is set to 0, which I presume means no timeout.

DBCC CHECKDB runs from SSMS just fine.


texts:
We're preparing a database design for an ERP. The ERP are composed from several modules and every module has his proper db, which can deployed at least on one personnel schema.

When we have the choice to choose between deploying the app on 2 databases on the same physical server or on 2 schemas always on the same server, which strategy we have to choose or use?

We're using PostgreSQL 9.4 and Jboss server.


texts: Get previous field's value in MySQL trigger

texts:
I am using a MySQL database and I have this kind of table.

IDRefHandler
1 x    299
2 x    300
3 x    301

But I want to create a trigger (before insert) which will set automaticaly the Handler field. How to select the previous Handler value and just add one with a trigger ?

Is there any better way that storing the result of SELECT Handler FROM table order by Handler DESC LIMIT 1 and storing this in a VAR.
Is'nt it more efficient to use a magic word like OLD ? Or do I have to use a specific trick for this case?

PS : OLD is not usable in this case (source)


texts:
I am using CREATE OR ALTER in the scripts for all of my views and stored procedures, as they are pushed frequently from a CD pipeline.  I am also using replication to push certain tables, views, and stored procedures to several SQL Server subscribers.  Recently, one of my remote devs noticed that  changes to views and stored procedures were not being replicated even after they have been added as articles.  From what I can tell, ALTER VIEW or ALTER PROCEDURE should both be triggering replication updates which led me to believe CREATE OR ALTER would operate in the same way. I ran some manual tests while watching the replication monitor and it only appears to be triggering the DDL change to subscribers when I use just ALTER, but not if I use CREATE OR ALTER.
Is this expected behavior?


texts: Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

texts:
Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly.

But today, when I executed the same script, it threw a wierd exception in SQL Server:

Altering dbo.YourTableHere...

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 367
The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 191
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The statement which threw the problem is:

PRINT N'Altering dbo.YourTableHere...';
GO
ALTER TABLE [dbo].[YourTableHere]
         ADD [a_new_timestamp] TIMESTAMP NOT NULL;

Is there anything I can configure to make my script run correctly without these warnings?

The computer I am running this script is acting as subscriber and it has a Replication that should be deleted?


texts:
I created an event to run every 24 hours and be run at 14h. Its selects some data from tabels and writes it as to a file (csv format)

The query and writing to file goes ok. The event is triggered.

But (there is always a but) the event is triggered at different intervals.

It runs at 21h and 18h and sometimes at 0h. It seems it runs at will.

Am I doing something wrong?
Is there a setting I need to change?

this is the event

'def', 'x54_pro_dbo', 'export_timepro_to_genius', 'notes@%', 'SYSTEM', 'SQL', 'call     export_timepro_to_genius', 'RECURRING', NULL, '24', 'HOUR', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION', '2012-01-26 14:00:00', '2036-01-26 00:00:00', 'ENABLED', 'PRESERVE', '2012-01-26 12:51:43', '2012-01-26 12:51:43', '2012-01-29 21:00:01', ' export timepro to genius', '0', 'utf8', 'utf8_general_ci', 'latin1_swedish_ci'

This is how I created the event

drop event if exists export_timepro_to_genius;
  CREATE EVENT IF NOT EXISTS  export_timepro_to_genius
   on  SCHEDULE EVERY 24 HOUR STARTS '2012-01-27 14:00:00'
   on completion preserve
   comment ' export timepro to genius'
 do  call export_timepro_to_genius  ;

I'm running mysql 5.5.6-rc

This is is from the .err log. So you can see that the query is runned at 14h and creates the file and then it is run again at 21h and gives an error because it cannot overwrite the file (witch is not the problem, the problem is that the event is triggerd at 21h)

120127 21:00:00 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120127.csv' already exists
120127 21:00:00 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120127 21:07:14 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120127.csv' already exists
120127 21:07:14 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120128 18:07:14 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120128.csv' already exists
120128 18:07:14 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120128 21:00:00 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120128.csv' already exists
120128 21:00:00 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120129 15:07:16 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120129.csv' already exists
120129 15:07:16 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed.
120129 21:00:01 [ERROR] Event Scheduler: [notes@%][x54_pro_dbo.export_timepro_to_genius] File 'c:/tmp/TimePro20120129.csv' already exists
120129 21:00:01 [Note] Event Scheduler: [notes@%].[x54_pro_dbo.export_timepro_to_genius] event execution failed


texts: Stored procedure is running slow but it used to perform well

texts:
http://pastebin.com/index.php?e=1I know you might have heard about this issue so many times or at least once in your career but this has become a huge performance pain in our environment.

Database size: 1 TB after compression Table Size: 800 GB with more than billion records

Application usually queries the stored proc, When they run the report, it usually calls a stored proc and it eventually queries huge view ( it has close to 900 columns and one of the table in the view is above mentioned 800GB table ).

Environment Overview:

SQL 2014 enterprise, Always On No table partitioning ( planning to implement this year ) Windows 2012 Standard Memory : 512, 490 GB allocation to SQL Server CPU : 16 cores

Issue: when a report was run, it calls a stored proc and the stored proc ultimately generates dynamic SQL based on the user select dynamic parameters. So, problem here is for every execution it generates new plan in the plan cache.

when ever the issue arise, we tweak the code inside by adding/removing some filters as a temporary work around but what we are looking here is to find a definitive solution to this problem.

Things we have done:

We have setup a different customized update stats job based on rows modified with sample percent for each indexes in that huge table.
We don't rebuild indexes on this huge table because of the amount of time but we do reorganize based on page count.
Created filtered statistics on the problem table ( large table ) based on the recommendation from SQLSkills because of skewed data ( we are working in parallel with SQL Skills team )

Things to do:

Table Partitioning -
I'm thinking of creating filtered indexes. If you have encountered any of these issues, please let me know your thoughts or suggestions.


texts: Optimize query or model, to get an population overview in time

texts:
I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance.

I wonder if there is a trick in my model, or MySQL query, to speed up the execution time to get an overview of my population at a specific time.

I have the following table which holds the status (history) of my users:

CREATE TABLE `status` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` varchar(9) NOT NULL,
 `status` enum('NEW','REGISTERED','CANCELED') NOT NULL,
 `timestamp` datetime NOT NULL,
 `explanation` varchar(25) NOT NULL,

 PRIMARY KEY (`id`),
 KEY `index1` (`user_id`,`timestamp`,`id`),
 KEY `index2` (`timestamp`,`user_id`,`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

My table will hold a lot of users (5 million), which can have one, or more statuses (so the table will probably hold 10+ million records).
Records are inserted in chronological order, so ID and timestamp are "in sync" (timestamps are not required to be unique, but the autoincrement ID's are)

What I do to get an overview of my population at a specific time is:

SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;

Which gives me the correct ID's, but since MySQL cannot use indexes when sorting on a column (for the where clause), which is not in the GROUP BY, there is no performance.

The explain tells me:

explain SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                                                     |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | range | index2              | index2              | 8       | NULL | 13897517 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+

Does anyone knows how I can optimize my model, or my query (for instance with subqueries) (, to allow MySQL to make use of indexes), and so boost performance?

What I eventually want is:

SELECT user_id, status, timestamp, explanation FROM `status` WHERE id IN (
  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id
);


texts: Change data capture - how to know who made the change?

texts:
Tracking who made the change identified by CDC.

Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table.  But, that seems to return the owner of the cdc process and not the user who initiated the change on the base table.  I also tried original_login but that returns the sql service account login.  Again, likely associated with the cdc process and not the user who initiated the change.

I found a similar question on stack-overflow but with no answer other than tracking changes from the front end or via a trigger, which seems to defeat the purpose of using cdc.  I wouldn't repost but since the original was on stackoverflow I thought I'd give it a try here especially if R2 or 2012 has introduced a better way.

So, in short:  How do I know who made the change in change data capture?


texts:
There are 2 parts to my question.

Is there a way of specifying the initial size of a database in PostgreSQL?
If there isn't, how do you deal with fragmentation when the database grows over time?

I've recently migrated from MSSQL to Postgres, and one of the things we did in the MSSQL world when creating a database was to specify the initial size of the database and transaction log. This reduced fragmentation and increased performance, especially if the "normal" size of the database is known beforehand.

The performance of my database drops as the size grows. For example, the workload I'm putting it through normally takes 10 minutes. As the database grows, this time increases. Doing a VACUUM, VACUUM FULL and VACUUM FULL ANALYSE do not appear to solve the issue. What does solve the performance problem is stopping the database, de-fragmenting the drive and then doing a VACUUM FULL ANALYSE takes the performance of my test back to the original 10 minutes. This leads me to suspect that fragmentation is what's causing me pain.

I've not been able to find any reference to reserving tablespace/database space in Postgres. Either I'm using the wrong terminology and thus finding nothing, or there is a different way of mitigating filesystem fragmentation in Postgres.

Any pointers?

The Solution

The supplied answers helped confirm what I'd begun to suspect. PostgreSQL stores the database across multiple files and this is what allows the database to grow without worry of fragmentation. The default behaviour is to pack these files to the brim with table data, which is good for tables that rarely change but is bad for tables that a frequently updated.

PostgreSQL utilizes MVCC to provide concurrent access to table data. Under this scheme, each update creates a new version of the row that was updated (this could be via time stamp or version number, who knows?). The old data is not immediately deleted, but marked for deletion. The actual deletion occurs when a VACUUM operation is performed.

How does this relate to the fill factor? The table default fill factor of 100 fully packs the table pages, which in turn means that there is no space within the table page to hold updated rows, i.e. updated rows will be placed in a different table page from the original row. This is bad for performance, as my experience shows. As my summary tables get updated very frequently (up to 1500 rows/sec), I opted to set a fill factor of 20, i.e. 20% of the table will be for inserted row data and 80% for update data. While this may seem excessive, the large amount of space reserved for updated rows means that the updated rows stay within the same page as the original and there's a the table page isn't full by the time the autovacuum daemon runs to remove obsolete rows.

To "fix" my database, I did the following.

Set the fill factor of my summary tables to 20. You can do this at creation time by passing a parameter to CREATE TABLE, or after the fact via ALTER TABLE. I issued the following plpgsql command: ALTER TABLE "my_summary_table" SET (fillfactor = 20);
Issued a VACUUM FULL, as this writes a completely new version of the
table file and thus by implication writes a new table file with the
new fill factor.

Rerunning my tests, I see no performance degradation even when the database is as large as I need it to be with many millions of rows.

TL;DR - File fragmentation wasn't the cause, it was table space fragmentation. This is mitigated by tweaking the table's fill factor to suit your particular use case.


texts: Is updating with value using a subquery bad in performance?

texts:
I have made an update query in the following form:

update table_a
set some_column = (
    select very_complicated_expression_involving_multiple_columns_in_table_b
    from table_b
    where table_a.id = table_b.id
)

If table_a is large, will the database engine loop the select query for each row in table_a? Should I rewrite this to an update from statement?


texts: Can an SSIS variable have a default value?

texts:
I have to migrate a SQL Server 2000 database to version 2008. A large part of that work is to update all the DTS packages to SSIS. Many of the packages require using the current date to establish parameter settings as well as date-stamping files (i.e. myoutputYYYYMMDD.csv, for example). This logic has been ported over to the new SSIS packages but the problem I'm having is now during testing.

Backups and restores from the old to the new server is a bit problematic due to its size but since the packages do archiving, I could take an older backup and run tests against that and compare package results with some archived files, in lieu of real-time comparisons.

Since this is a better logistical solution, I need to redesign the packages where any of the package variables depend on the usage of GETDATE() and need to reference a new date variable that will be GETDATE() once we roll out to production but needs to be a different date during testing (e.g. I restore a 1/2/2014 and then pass in 1/3/2014 to my date variable during testing but in live production use, it will be GETDATE()).

Is there an elegant way to do this?


texts: What administration utilities does Ubuntu and Debian package with PostgreSQL?

texts: I see that there are a lot of tools authored by Ubuntu and Debian for PostgreSQL? Does any one have a list of them, or the build-differences?


texts: How unique does AUDIT_GUID need to be when creating a server audit?

texts:
When you use the GUI to create a server audit it creates a password worthy (no caps) value like

e5abe6c5-f451-41f3-936c-b1da1c0786ce

When you look to CREATE SERVER AUDIT with Transact-SQL it does not say much

AUDIT_GUID = uniqueidentifier
To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID cannot be modified after the audit has been created.

You can have multiple audits on the server.  So it can't be null, and it looks like you can do some stuff cross server (i.e. auditing A from B) so it probably needs to be unique across your network. If you have mirroring it needs to the same on the mirrored instances. You give the audit a name when you create it.
CREATE SERVER AUDIT [AuditSelect]

There is a suggestion on StackExchange to Dynamically Generate a AUDIT_GUID value via T-SQL.
I am thinking that name of the 'primary server' + 'Audit Name' should be fine as a unique value; Server01_AuditSelect.
Assuming a 1000+- servers, is there anything significant am overlooking?
Assuming you can't have multiple active servers in the same network with the same name.


texts: How to get difference in value over 15min interval?

texts:
I have this table:

The 2 important columns here are domainid and Soldtickets.

Soldtickets show how many tickets were sold today.

I make a call to my server and it tells me that I made the call
at "2016-03-30 14:18:01.4130000" and that 0 tickets have been sold for the
Domain with DomainId 53.

I am trying to figure out how I can find the difference.

I call the server every two minutes and I get data.
I want to check the data that I have got the last 15 minutes and see if soldtickets has changes and how much.

Example:

  At :

  2016-03-30 14:18:01.4130000

  For DomainId

  53

  0 Tickets Sold

  At :

  2016-03-30 14:26:01.4130000

  For DomainId

  53

  5 Tickets Sold(5 tickest total for today)

  At :

  2016-03-30 14:30:01.4130000

  For DomainId

  53

  5 Tickets Sold ( same as before, NO change)

I want to know how many were sold the last 15 minutes.
In this case it would be 5 tickets but what if at

2016-03-30 14:44:01.4130000
I get back that I have sold a total of 7 tickets for today, then the difference for the last 15 minutes is 2 tickets.

So far I can get the inserts of the last 15 minutes like so:

DECLARE @LatestFifteenMinTickets TABLE
(
    DomainId bigint,
    SoldTickets bigint
)

DECLARE @FifteenMinDate datetime2

SELECT @FifteenMinDate = DATEADD(minute, -15, GETDATE())

INSERT INTO @LatestFifteenMinTickets(DomainId,SoldTickets)
            SELECT DomainId, SoldTickets
            FROM DomainDetailDataHistory
            WHERE [Date] > @FifteenMinDate

I have tried a few scenarios but I honestly don't know how to conntinue.

How can I do this?

EDIT:

  Lets say If i get back that I have sold 0 tickets today at 13:00. And
  the next 15 minutes I get 15 inserts where soldtickets is also 0. the
  diffirence here is nothing since 0-0 = 0 Which means I have sold 0
  tickets the last 15 minutes AND sold 0 tickets for the entire day (so
  far). But at 14:00 I get back info from server that 10 tickets are
  sold. The next 15 minutesI keep getting sold tickets = 10 which means
  that the last 15 min i have sold 0 tickets but for the entire day i
  have sold 10 tickets –

I Need to check the soldtickets columnt for every unique DomainId and see if there is any difference to the most current one. If there Is i want to know what the difference is in numbers


texts: How can I see a query after rules have been applied?

texts:
From docs - 37.3.1.1. "A First Rule Step by Step"

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

Now someone does:

(1) UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

And the parser generates this additional query

(2) INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

The question is: Are there any tools to tell how the query (1) gets rewritten into (1) + (2)?


texts:
Given the available documentation here:

https://docs.mongodb.com/manual/tutorial/modify-chunk-size-in-sharded-cluster/

There is no mention of the potential impact of memory (RAM) usage by changing the default chunk size. I am aware that MySQL (and other databases) always read rows into memory as pages if at least one is required, and wondered if a similar thing might happen in mongo?

If anyone could help shed some light on any possible relationship between chunk size and memory usage that would be greatly appreciated.


texts: How to represent a list of entities within a table of the same entity in PostgreSQL?

texts: I have a table called user with an attribute friendsList which has to be a list of ids pointing to different users. I really have no clue how to do this using Postgres


texts:
I have data like :

Emp Code | Name | Qualification | Completed Year
10000001   Ram    B.E               2005
10000001   Ram    M.E               2007
10000002   Sita   B.E               2003
10000002   Sita   MS                2005
10000002   Sita   Ph.D              2010

I want to convert like

Emp Code | Name |UG_Qualifi | Completed Yr| PG_Qualifi | Completed Yr
10000001   Ram    B.E               2005      M.E               2007
10000002   Sita   B.E               2003      MS                2005


texts:
I am a beginner and start designing small database. I've found that to automatically generate value from the table, I can either use a trigger or compute them when forward engineering.
Therefore, I've referred to:

https://stackoverflow.com/questions/5222044/column-calculated-from-another-column
https://gabi.dev/2016/10/17/understanding-generated-columns/

But what I am looking for is to compute the value from two tables. Is that possible to do SELECT to grab the value for generated values? Or I've done it wrongly. Any help or hint is highly appreciated.
So far, I've tried this, but it's wrong:
CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
`OrderNumber` INT NOT NULL,
`ShippingAddress ID` INT NOT NULL,
`OrderDate` DATE NOT NULL,
`Shipping Cost` INT NOT NULL,
`Customer_ID` INT NOT NULL,
 /* tried to get the value from two table */
`ShipWeight` INT GENERATED ALWAYS AS (
 SELECT SUM(Product.ProductCode*Quantity)
 FROM Order
 INNER JOIN Product on Product.ProductCode = Order.ProductCode) VIRTUAL,
`Quantity` INT NOT NULL,


texts: Adding Calculated Columns together to create a Total

texts:
I am creating a tracking sheet, based off of multiple tables. I then use a CASE statement to determine the progress of each section. I would like to then take the results of these 3 calculated col and create a Total ?

Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2],
(Val1 + Val2) as [Total]
From Table

Currently the Addition line is not working since the Val1 and Val2 are 'Invalid Column Names'

How can I fix this ?

SELECT
    d.DataSourceID as [DataSource],
    c.Name as [Custodian Name],
    et.Name as [Source Type],
    ed1.QCBarcode as [ED1 QC Barcode],
    ed1code.Name as [ED1 Status],
Case
    When ed1code.Name like '01%' then 14
    When ed1code.Name like '02%' then 28
    When ed1code.Name like '03%' then 42
    When ed1code.Name like '04%' then 56
    When ed1code.Name like '05%' then 71
    When ed1code.Name like '06%' then 85
    When ed1code.Name like '07%' then 100
    When ed1code.Name like '08%' then 0
End  as [ed1Calc],
ed3.ProcessingCase as [ED3 LAW Case Name],
ed3code.Name as [ED3 Status],
Case
    When ed3code.Name like '01%' then 50
    When ed3code.Name like '02%' then 100
    When ed3code.Name like '03%' then 0
    When ed3code.Name like '04%' then 0
End  as [ed3Calc],
ed4.ED4ID as [ED 4 Reference],
ed4code.Name as [ED4 Status],
Case
    When ed4code.Name like '01%' then 34
    When ed4code.Name like '02%' then 66
    When ed4code.Name like '03%' then 100
End  as [ed4Calc],
 ( (ed1Calc + ed3Calc + ed4Calc)/3 ) as [Overall Status]
/*Basic  layout */
FROM [EDDSDBO].[DataSources] d
JOIN  [EDDSDBO].[Custodian] c ON d.Custodian = c.ArtifactID
JOIN [EDDSDBO].[EvidenceType] et ON d.EvidenceType= et.ArtifactID
JOIN [EDDSDBO].[f4868106f4868107] fed1 on fed1.f4868107ArtifactID = d.ArtifactID
JOIN [EDDSDBO].[ED1PreProcessingRecord] ed1 ON fed1.f4868106ArtifactID = ed1.ArtifactID
JOIN  [EDDSDBO].[ED3LawLoadingRecord] ed3 ON ed1.ArtifactID = ed3.ED1Reference
JOIN [EDDSDBO].[ED4LawRelativityMigrationRecord] ed4 ON ed3.ArtifactID =    ed4.ED3Reference
 /*status*/
--ed1
JOIN [EDDSDBO].[ZCodeArtifact_1000129] ed1Status  ON ed1.ArtifactID =  ed1Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed1code ON ed1Status.CodeArtifactID = ed1code.ArtifactID

--ed3
JOIN [EDDSDBO].[ZCodeArtifact_1000156] ed3Status  ON ed3.ArtifactID = ed3Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed3code ON ed3Status.CodeArtifactID = ed3code.ArtifactID
--ed4
JOIN [EDDSDBO].[ZCodeArtifact_1000161] ed4Status ON ed4.ArtifactID = ed4Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed4code ON ed4Status.CodeArtifactID = ed4code.ArtifactID


texts: Mirror database on same server instance for better read performance?

texts:
I am developing a somewhat big web application, which uses data which is aggregated using a crawler. The crawler is constantly writing to the database and updates many tables at the same time. For the frontend it is not important to have the very latest data available all the time, but the response time is critical for the user experience. The frontend accesses the same database (read-only), but is really slow as long as the crawler is active. I suspect that unfortunate locks on the database cause this, as the server still has many CPU cores idling and iostat also shows that the HDDs aren't fully utilized yet.

I know that I could probably resolve this issue by creating a master/slave setup using another database server, which is replicated periodically, but I wonder if I could achieve the same performance jump in another way:

Does it make sense to replicate/mirror a database (mirror all the tables needed for the application) within the same database-server-instance? If so, what would be the smartest way to do so?

(I am using postgreSQL 8.4.17 if that makes any difference in this case)


texts: Recommendation for PGA_AGGREGATE_TARGET in ASMM

texts: What can be the value of PGA_AGGREGATE_TARGET if we configure the ASMM in oracle 12c ?.Is there any recommendation for this one.Because the sga is only tuned in ASMM.


texts: Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level

texts:
I'm using Oracle 11g (unfortunately not 12c, i.e. OFFSET n ROWS FETCH NEXT m ROWS ONLY is not available) and I'm trying to achieve pagination on a hierarchical query.

The aim is to sort the root level entries and retrieve for a given number of root entries all connected children. The pagination (offset and limit) should be performed by the root entries.

Is there any way to achieve this?

I managed to limit the number of root entries but I cannot achieve the sorting on the root level (START WHITH) or specify the offset only for the root level.

SELECT *
FROM my_table
START WITH (
    column_a = 'some value'
    AND column_b = 'some other value'
)
AND ROWNUM <= 10 -- limit rows on top level, however the sorting is arbitrary
                 -- and offset is not possible
CONNECT BY
    PRIOR childid = parentid
    AND column_b = 'whatever'
ORDER BY sort_column  -- applies to overall result, alternatively you could
                      -- use ORDER SIBLINGS BY to sort the entries by their level

Of course it is possible to to some pagination on the overall result by using a subselect like this:

SELECT * FROM (
    SELECT *, rownum as row_num
    FROM my_table
    START WITH (
        column_a = 'some value'
        AND column_b = 'some other value'
    )
    CONNECT BY
        PRIOR childid = parentid
        AND column_b = 'whatever'
    ORDER BY sort_column
)
WHERE row_num BETWEEN <offset> and <offset+limit>

however, this statement would not limit the number of root entries but the overall entries, i.e. it chops off child nodes, as I do not know, how many children exist for a root node.


texts: View SQL Code Behind Flat File Import Wizard?

texts:
Is there a way to view and copy the code that the "Import Flat File to SQL" Wizard ultimately uses, after I have gone through all the steps (chosen file, set up data types for my columns, etc.)?
While using the Import Flat File to SQL wizard, I found it cumbersome to have to select a data type for each column individually. My data had a large amount of columns to change. In the future, I would prefer doing this via SQL code to save time. I'm learning Microsoft SQL Server Studio and currently only know how to use the Import Flat File Wizard, so I would need to learn all the steps for doing it another way.
Version: 18.7.1


texts:
I would like to store the database size for a particular database on daily basis on to a table which would help in understand the growth of the database over a period of time.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES where table_schema = 'testdb' GROUP BY table_schema;

I get the output for the above query say 10456.3.

I need to insert this number on to a table which should contain three columns.
DB name, Date and DB size.

How can I do this on daily basis by automating this?


texts: Why is enabling db_chaining bad from a security perspective?

texts:
I have two databases, Database1 & Database2 and two applications that access both databases using an account called AppUser. There are hundreds of views in Database1 that access tables in Database2.

So far in the current model, AppUser has been granted access to these tables in Database2.

I want to use DB_CHAINING ON in Database1 & Database2, and allow access to Database2 tables only through sql objects in Database1. After all AppUser doesn't need direct access to tables in Database2.

Currently the sql objects in the two databases can only be created by DBAs & developers. There are no other human business users that can create schemas/sql objects in Database1 & Database 2.

I keep reading that enabling DB_CHAINING is a security risk.

Here for instance they make the claim:

  Let it be said directly: this method opens a security hole that
  permits a local power user to elevate his permission to sysadmin,
  although the way to do it is a little more complicated and roundabout
  than when the database is set to TRUSTWORTHY.

Can you please give an example of security risks I am exposing myself to in case I use DB_CHAINING ON for the two databases? As far as I am concerned, it is safer since AppUser won't have access directly to tables in Database2.

One security risk that I identified is accidentally allowing other users to create views that can access the dbo owned tables. In my case it's not an issue because no other human business users are allowed to create sql objects   in the two databases.

There is another post here where it has been suggested the use of option (4) where one signs the sql objects (note that views cannot be signed only stored procedures & functions). While it is the most secure option, it's also one of the most convoluted solutions that the geniuses from Microsoft came up with. I used it already in a different context but honestly I think it is overkill.

Update:

One serious risk that I identified is having a third database, Database3 with DB_CHAINING ON and with the same owner as Database1 & Database2. A db_owner user in Database3 would be able to create views/stored procedures that return data from any tables in Database1 & Database2 even though the user might not be able to access those tables directly, and as long as his login is mapped to a user in Database1 & Database2. This issue is avoidable by making sure the Database3 db_owner doesn't have access at all on Database1 & Database2.

Update 2:

I decided not use the DB_CHAINING ON. But the more I think about it, the sql server model forces one to allow wider permissions than necessary. It is not uncommon to have multiple databases mapped to business areas/contexts and often it is necessary to have views that access data in multiple databases. Views cannot be signed. If a view in database1 selects data from a table in database2 I have to grant the user select on that table in database2 even when database1 & database2 have the same owner.


texts: altering a db2 table with huge amount of data

texts:
I have a table in DB2 which is having 99 columns and 900,000 records of data.

I want to alter the table with one extra column as below:

ALTER TABLE MYSCHEMA.PRODUCT ADD UPDATE_DT DATE

However, the query never completes and the status of the table shows locked against my id in the APPLICATION LIST window from where I have to force kill it.

I believe this is because of the huge amount of data present.

Am I missing anything here ? How can I achieve what I am trying to do ?


texts:
I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside.

The process worked fine and no errors happens during the load of a huge sql file (10K+) with sqlplus. But on the last step the process breaks. I could pinpoint the issue to the following line for recompilation:

@?/rdbms/admin/utlrp.sql

The error received is the following:

ERRORS DURING RECOMPILATION
---------------------------
                          0

Function created.

   p_schemaname  dbms_id;
                 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 5, column 18:
PL/SQL: Item ignored
ORA-06550: line 6, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 6, column 18:
PL/SQL: Item ignored
ORA-06550: line 62, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 62, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 65, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 64, column 12:
PL/SQL: Statement ignored

Function dropped.

I tried to find an answer on google but the best I could find is that there are some invalid objects. Which I couldn't find any.

SQL> SELECT owner,
  2         object_type,
  3         object_name,
  4         status
  5  FROM   dba_objects
  6  WHERE  status = 'INVALID'
  7  ORDER BY owner, object_type, object_name;

no rows selected

I am executing the process as sys with sysdba. Maybe the re-compilation is not necessary but I cannot remove it from the script.

A week ago I tried to do the same thing on a "real" Oracle 11g installation without docker. There the script worked. So I assume that there is something wrong with the container but I am not sure what exactly.

Did anybody encounter this issue before, do you have any ideas?

Thanks in advance

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


texts: How to access pg_settings from separate connection

texts:
I am trying to access the pg_settings table from a separate connection belonging to a legacy application.

I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to get to its pg_settings table from here.

I've checked pg_stat_all_tables, but that has no reference to any pg_settings tables.

information_schema.tables has a single row for pg_settings with table_type set to VIEW which indicates to me that I should be looking for a view rather than a table.

How can I access pg_settings belonging to a separate connection?


texts: RETURN TABLE is converted to RETURN SETOF int in Postgres function

texts:
In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE:

CREATE OR REPLACE FUNCTION test(
    IN parm1 date,
    IN parm2 interval)
  RETURNS TABLE(identifier INT)
AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql;

After the function is created, pgAdminIII shows the function as having the following definition:

CREATE OR REPLACE FUNCTION test(IN parm1 date, IN parm2 interval)
  RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

When executing the function it follows the logic of the RETURNS TABLE. But, when editing the function it will pickup the RETURNS SETOF. This provides different functionality than the function I specified (e.g. the name of the returned column no longer exists).

So, how can I force pgAdminIII to keep the returned column name of identifier? I don't particularly care whether I define it as a TABLE or SETOF.


texts: How to select rows which have max and min of count?

texts:
I have the following table in SQL Server 2012:

CREATE TABLE [MyTable] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [MyGroup] INT NOT NULL
);

I want to output a table of the form

    | MyGroup | Count
Max |         |
Min |         |

For example, if I have 30 rows in MyTable where MyGroup = 1, 20 rows where MyGroup = 2, and 10 rows where MyGroup = 3, then

    | MyGroup | Count
Max | 1       | 30
Min | 3       | 10

What sort of query would output this information?


texts: Why is :r SQLCMD command marked as wrong in Post Deployment Script?

texts:
I have worked a couple of times with post deployment scripts and always, intuitively used the build action "PostDeploy", because that is what it is.
Now for the first time I try to follow the built-in instruction from the script's template to use the ":r somescript.sql" syntax.

Immediately this line is getting marked as wrong:

  "SQL80001 wrong syntax next to ':'"

I found suggestions to set the PDS to Build Action "none". This does not help, the error stays. What am I missing here?


texts: insert all record into table1 from table2 using where like clause

texts:
I have 2 tables

Table1

ID - Metrics - t000
1 - chattervolume - BTC0908473
2 - negsent - NULL

Table2

ID - Metrics - t000

I want to insert Table1 data to Table2 using WHERE LIKE t000 '%BTC%' clause and If any column of field t000 not found anything in LIKE clause then it inserts N/A in column


texts: MySQL: Load data with FK constraint into existing tables with same ids values already used

texts:
I have these tables:

table1_main
table2_main

table1_test
table2_test

table1_main and table1_test have identical structure and table2_main and table2_test also have the same structure.

table2_main has FK referencing table1_main and table2_test also has FK referencing table1_test

Now I want to move records from the *_test tables into the equivalent *_main tables but the *_main tables already have data with existing IDs which will conflict with the IDs that are present in the *_test tables.

The only solution that I can think of is inserting row from table1_test, retrieve new inserted id, then update the corresponding rows of table2_test and then insert them into table2_main.

I was wondering if there are other alternatives.


texts: DB2 multiple on delete triggers causing deadlock

texts:
When trying to run a delete query on the parent table I am getting locked. Following is the table structure and executing this " DELETE FROM PARENT WHERE PARENT_ID = 1; " would create a deadlock -

------------------Create related tables-----------------------------

CREATE TABLE PARENT (
    PARENT_ID INT NOT NULL,
    PARENT_NAME VARCHAR(100) NOT NULL
);
CREATE TABLE CHILD_ONE (
    CHILD_ONE_ID INT NOT NULL,
    CHILD_ONE_NAME VARCHAR(100) NOT NULL,
    SELF_REFERNCE_ID INT,
    LANGUAGE_ID INT NOT NULL
);
CREATE TABLE CHILD_TWO (
    CHILD_TWO_ID INT NOT NULL,
    CHILD_ONE_ID INT NOT NULL,
    QUANTITY INT,
    LANGUAGE_ID INT NOT NULL
);

-----------------------Create indexes on tables-----------------------------

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (PARENT_ID);
ALTER TABLE CHILD_ONE ADD CONSTRAINT CHILD_ONE_PK PRIMARY KEY (CHILD_ONE_ID,LANGUAGE_ID);
ALTER TABLE CHILD_ONE ADD CONSTRAINT CHILD_ONE_ID_FK FOREIGN KEY (CHILD_ONE_ID) REFERENCES PARENT (PARENT_ID)
ON DELETE CASCADE;
ALTER TABLE CHILD_ONE ADD CONSTRAINT SELF_REFERNCE_ID_FK FOREIGN KEY (SELF_REFERNCE_ID) REFERENCES PARENT (PARENT_ID)
ON DELETE CASCADE;
ALTER TABLE CHILD_TWO ADD CONSTRAINT CHILD_TWO_PK PRIMARY KEY (CHILD_TWO_ID,LANGUAGE_ID);
ALTER TABLE CHILD_TWO ADD CONSTRAINT CHILD_TWO_ID_FK FOREIGN KEY (CHILD_TWO_ID) REFERENCES PARENT (PARENT_ID)
ON DELETE CASCADE;
ALTER TABLE CHILD_TWO ADD CONSTRAINT CHILD_ONE_ID_FK FOREIGN KEY (CHILD_ONE_ID) REFERENCES PARENT (PARENT_ID)
ON DELETE CASCADE;

-----------------------------Create triggers tables-----------------------------

CREATE TRIGGER CHILD_ONE_TRIGGER AFTER DELETE ON CHILD_ONE
REFERENCING OLD AS O FOR EACH ROW
DELETE FROM PARENT WHERE PARENT_ID = O.CHILD_ONE_ID;

CREATE TRIGGER CHILD_TWO_TRIGGER AFTER DELETE ON CHILD_TWO
REFERENCING OLD AS O FOR EACH ROW
DELETE FROM PARENT WHERE PARENT_ID = O.CHILD_TWO_ID;

---------------Insert Records--------------------------------------------

INSERT INTO PARENT (PARENT_ID, PARENT_NAME) VALUES (1,'CHILD_ONE-1');
INSERT INTO PARENT (PARENT_ID, PARENT_NAME) VALUES (2,'CHILD_ONE-2');
INSERT INTO PARENT (PARENT_ID, PARENT_NAME) VALUES (3,'CHILD_ONE-3');
INSERT INTO PARENT (PARENT_ID, PARENT_NAME) VALUES (4,'CHILD_TWO-1');
INSERT INTO PARENT (PARENT_ID, PARENT_NAME) VALUES (5,'CHILD_TWO-2');

INSERT INTO CHILD_ONE (CHILD_ONE_ID, CHILD_ONE_NAME, SELF_REFERNCE_ID, LANGUAGE_ID) VALUES (1,'CHILD_ONE-1', NULL, 1);
INSERT INTO CHILD_ONE (CHILD_ONE_ID, CHILD_ONE_NAME, SELF_REFERNCE_ID, LANGUAGE_ID) VALUES (1,'CHILD_ONE-1', NULL, 2);
INSERT INTO CHILD_ONE (CHILD_ONE_ID, CHILD_ONE_NAME, SELF_REFERNCE_ID, LANGUAGE_ID) VALUES (2,'CHILD_ONE-2', 1, 1 );
INSERT INTO CHILD_ONE (CHILD_ONE_ID, CHILD_ONE_NAME, SELF_REFERNCE_ID, LANGUAGE_ID) VALUES (3,'CHILD_ONE-3', 2, 2 );

INSERT INTO CHILD_TWO (CHILD_TWO_ID, CHILD_ONE_ID, QUANTITY, LANGUAGE_ID) VALUES (4, 1, 501, 1);
INSERT INTO CHILD_TWO (CHILD_TWO_ID, CHILD_ONE_ID, QUANTITY, LANGUAGE_ID) VALUES (5, 2, 501, 1);
COMMIT;


texts: new Install and migrate from mysql to mariadb

texts:
I have a server landscape,
running mysql 5.7 on debian 8 in some
GTID based replication configuration.

All is high load servers and 200+GB databases.

Now I got some new servers.
I like to run that new servers on debian 9 and the recommended mariadb 10.2.X
expanding it later to a MariaDB Galera Cluster.

I've read some, how to migrate from mysql to mariadb, but anyhow none of them seems to fit for me.

Different GTID handling and innodb format between mysql and mariadb does scare me a bit.

After all I have to run some tests.

So, my idea is:

on one of the masters (mysql 5.7),

-flush tables with read lock

-make a snapshot (LVM) of the database partition

-show master status to get the GTID executed

-unlock tables

-copy that snapshot to a new server to /var/lib/mysql

-install mariadb on the new server

would mariadb upgrade that data in /var/lib/mysql

Any recommendation or another ideas on that?

Ju


texts: The target table user of the INSERT is not insertable-into MariaDB 10.4.11

texts:
I did a wonderful thing of deleting all root users from mysql by accident. That kind of a day.

However before I panic I figured I would use this method here from searching online that didn't seem so bad.

"Add 'skip-grant-tables' to my.cnf under the [mysqld] section restart mysql"

then insert a new root user using "INSERT INTO mysql.user..."

When I do this I get an error I cannot find much information about

The target table user of the INSERT is not insertable-into
Does anyone have any ideas about this and how can I re-create a root user?

at first I thought this might have something to do with virtualmin because they talk about it here but I did this from CLI into mysql and it still says the same error

my environment

Centos7 Webmin version 1.940
Usermin version|1.780
Virtualmin version 6.08
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1


texts: PostgreSQL Export to CSV. Each element of array in new line

texts:
I have next request

\copy (
  SELECT users.name, ARRAY_AGG(emails.email), ARRAY_AGG(addresses.address)
  FROM users
  LEFT JOIN emails USING(id)
  LEFT JOIN addresses USING(id)
  GROUP BY users.email
) TO 'path/to/file.csv' WITH csv header;

and it exports data in next way

|John Doe|{email1@qq.qq,email2@qq.qq}|{addr1,addr2}|

is it possible to export array in multiline cell? To have next view

-----------------------------
|John Doe|aaaaa1@aa.aa|addr1|
|        |aaaaa2@aa.aa|addr2|
-----------------------------
|Foo Bar |bbbbb1@bb.bb|addr9|
|        |bbbbb2@bb.bb|addr8|
|        |bbbbb3@bb.bb|     |
-----------------------------

I've tried to join array into a string with \r\n, but it doesn't work

Is it possible at all?


texts: Сopy rows from one table to another and replace duplicates in the process

texts:
I have a SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a stored procedure is executed to copy all rows from the raw table into a production table, whilst performing the necessary castings, handling of dates, etc. Here is a simplified example of the stored procedure code:

USE [example_database]
GO

/****** Object:  StoredProcedure [dbo].[Append_Data]    Script Date: 19/07/2016 11:21:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Append_Data] as
--Code to delete updated records

--Code to perform the append of incremental records
insert into dbo.[Admissions]
SELECT [refno]
      ,[name]
      ,[value]
      ,cast([modified_date] as datetime)
  FROM [RAW].[Admissions]

GO

I need some help to amend this code so it can handle duplicates as follows: Based on the column that uniquely identifies each row (named refno -- a candidate key), I need to replace any existing records with the same refno in the production key, as long as the modified date column (named date_modified -- a datetime column) is bigger (newer) than what is already in the production table.

Here is an example:

PRODUCTION TABLE

refno, name, value, date_modified
1, Alex, 2112.34, 08/Jul/2016 15:37:00
2, Geddy, 674.91, 08/Jul/2016 15:37:00
3, Neil, 914.73, 08/Jul/2016 15:37:00

RAW TABLE

refno, name, value, date_modified
1, Alex, 1001.93, 10/Jul/2016 12:27:00
2, Geddy, 674.91, 08/Jul/2016 15:37:00
3, Neil, 425.85, 01/Jul/2016 05:13:00
4, Hugh, 9807, 01/Jan/2016 13:42:00

The first row should get replaced in the production table because a row with the same refno exists in the raw table, and it has a newer modified date.
The second row has not been modified, so we leave it as is. Even though the matching row does exist in the target production table, we can see that the date_modified is the same so we ignore it.
The third row has been modified, but the modified date is earlier than what we have in the production table, so we ignore it.
The fourth row in the staging table is new (i.e. a row with that refno does not exist in the production table yet) so we copy it over.

Does that make sense? We don't tend to get many rows per day (about 1000 tops) and it is a straightforward, denormalised table (hence the stored procedure rather than going nuts in SSIS :) ).

Any help is greatly appreciated.


texts: How to model the relationships (a) between properties and owners and (b) between properties and tenants?

texts:
I have a database design and I'm having trouble with two tables. I have a Users table, and a Properties table. The Properties table may only have 0, 1, or 2 users, let's call them owner and tenant, and only one of each (per property), and a User may be an owner or a tenant in many Properties.

So, here are my two options according to the case:

Have two foreign keys in the Properties table (since the maximum of users referenced by a property are 2 and not N), called owner_id and tenant_id which reference the Users table.
Have a N:M intermediate table, with the user_id and property_id, along with another column to indicate the type of user in that property (say boolean 1 = owner, 0 = tenant).

Which is the best design option?

Clarifications:

A user has a name and email, and a property a street and its number as attributes.
A user may be only an owner or tenant in a property, not both, since an owner implies he owns the property (wether he lives there or not), and a tenant is another user actually living there, so there needs to exist an owner before a tenant is added.
A property may have no users assigned.


texts:
I would like to know the SQL Server cluster node fail over details immediately  as soon as the passive node is active as I have some dependency on active node. For that, I see sys.dm_os_cluster_nodes view will provide the active/passive node details, but I am not able to write a trigger on this view to send a mail automatically.
I spent some time but was not able to find out. I can write a SQL Server job to monitor but I will not prefer that.
Could you please help in guiding me to get a notification mail once the active node becomes passive?
Thank you.


texts: Migrating database from oracle 11.2.0.03 to 11.2.0.04.

texts:
I have been assigned task of migrating our oracle database from 11.2.0.03 to 11.2.0.04 version. Being new to oracle, I have list down all activities I need to carry out to make it successful. I will be creating new database in newer version and creating dump of my present database and export that to newer versions.
I have listed down below activity for same :

Need to take dump data from Existing  databases to new DEV, UAT and PROD databases.(Sequences also needs to be refreshed)

My confusion is, do I need to take care of more scenarios. Since I am not DBA, just a programmer, maybe I am missing some critical points.


texts: Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

texts:
I'm currently converting large tables to the Barracuda file format.  We are using file_per_table.

I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this.

However, the disk space used by MySQL is not decreasing.  How can I actually reclaim this disk space on the hard drive?  All I'm seeing is the table size when I run this query decreasing:

SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024 / 1024), 2) "Size in GB"
FROM information_schema.TABLES
WHERE table_schema = "database"
ORDER BY (data_length + index_length) DESC;

However, when I run df -h, I see that mysql is still taking up the same amount of space (these should be significant decreases).


texts:
I have been developing a (MySQL) database in the cloud for the past few months to link in to a web based service. Following a change to the roadmap the initial clients will be running on standalone systems utilising a local (host) database. Moving forward there will be a need to merge these databases and I am still trying to work out a strategy for doing this. I have done something like this previously and handled it by using different offsets in the primary keys for each database included in the merge.

However this is further complicated that some clients may continue to run locally for a period after the initial merge and require a re-merge or incremental merge. One suggestion made to me was seeding primary keys for each client but not how to do this. I have offset primary keys when merging but am apprehensive about offsetting them on a live database as ‘overlapping primary keys’ look more like an ‘if’ then a  ’when’!

The thought strikes me that maybe another SQL implementation may be more suitable than MySQL but if this is the case I need to migrate sooner rather than later.


texts:
I am running a SQL 2012 SP3 CU9 Standard server with 2 CPU's each having 4 cores (hyperthreading enabled). I have 384 GB RAM (Yes I know that I cannot use it all in SQL standard). Lock pages in memory is enabled.

My problem is that one of the CPU nodes seems to allocate all memory leaving just a little for the secound CPU node, causing one of the nodes to have a very low PLE and the other one a high PLE.

Did I configure something wrong?


texts: Sql Server Plan - what is a Filter after an Index operation?

texts:
I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL Server to do so, instead of doing all in the Index scan?

For an example:

The index already contains all the fields used by the WHERE. I expanded the Index Scan operation, and it execute part of the where with some parameters involved (totally expected). I then expanded and inspected the Filter operation, and what it does is to apply some other filtering on a couple datetime fields already evaluated by the Index Scan, plus some other conditions on some other SP parameters.

What's going on here? I obviously don't care about my specific query, that's not the point, I'm trying to understand what conditions can cause this to happen.


texts: How can I reclaim space after nulling out LONGTEXT records on MySQL?

texts:
I support an application with a table with a LONGTEXT column. (We actually support multiple DBMSes, but for the purposes of this question, I am asking about MySQL.)

As it turns out, the data in that column comprises a very large chunk of the total usage in the entire database (about 40%!), and that data is simply debugging information anyways.

Now, we want to keep the last month of debug data around, so we can't drop the column entirely, so I assume we can free up space by updating older rows to NULL.

My questions:

When I set the values of that column to NULL, does it actually delete the data? I know that LONGTEXT data is stored separately from the rest of the table. Are the now-orphaned entries "garbage-collected" or otherwise cleaned up at some point?
If this doesn't happen automatically, what do I need to do to trigger a cleanup?
Once the data is actually deleted, will that space be reclaimed by the OS/filesystem, or will it remain claimed by the database? If we wanted to release  the space back to the filesystem (so we can pay for a smaller hosted instance), how would we go about releasing that space?


texts: Optimizing a slow query to better use indexing

texts:
I have a query that looks like the following:

SELECT time_stop, some_count
        FROM foo
        WHERE user_id = 1
        AND time_start >= '2016-07-27 00:00:00'
        AND time_stop <= '2016-07-27 23:59:59'
        AND some_count = ( SELECT MAX(some_count)
          FROM foo
          WHERE user_id = 1
          AND tm_start >= '2016-07-27 00:00:00'
          AND tm_stop <= '2016-07-27 23:59:59'
       );

The table schema looks something like this:

Create Table: CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `time_start` datetime(6) DEFAULT NULL,
  `time_stop` datetime(6) DEFAULT NULL,
  `some_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),,
  KEY `user_id` (`user_id`),
  KEY `time_start` (`time_start`)
) ENGINE=InnoDB AUTO_INCREMENT=418005923 DEFAULT CHARSET=latin1

EXPLAIN output looks like the following:

+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys      | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |     1.67 | Using where |
|  2 | SUBQUERY    | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |    16.66 | Using where |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+

I'm using MySQL 5.7.11.

I think the major problem I'm having is there's very little cardinality in the indexed keys here. The query is attempting to get the largest value of some_count for a given date range. It also wants the exact datetime that the max value was met. This could return multiple rows, which returns to the little cardinality problem.

I'm not sure if I could re-write the query to better work with the existing indexes, I'm guessing I could though.


texts: best way for creating Index in mysql (multi-column/table)?

texts:
i have a big problem with my database..

I need a very fast query over 2 tables.
1. table: (node)   ~42.500.000 entrys
2. table: (waynode ~51.200.000 entrys

The following query is much to slow:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE,`waynode`.WAYID
    FROM  `node`, `waynode`
    WHERE  `node`.ID= `waynode`.NODEID
      AND  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  `waynode`.WAYID

The current time is over 180 seconds..

I also have a INDEX over 2 columns created with ALTER TABLE node ADD INDEX (LATITUDE, LONGITUDE).  When i am using the following select, it's very fast:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE
    FROM  `node`
    WHERE  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495'

But I need the waynode.NODEID like in the first SQL-Statement.
May someone help me please?

best regards
Ingo


texts: What performance advantages does append-only postgres allow?

texts:
Suppose we have a Postgres Database with only a single table. It is known that no UPDATE statements will ever be run on it, nor will any DELETEs. It is 100% append-only.

Some databases exist that rely on this behavior and manage to get specific performance gains as a consequence, such as Datomic. However, this is not the typical way Postgres is expected to be run so I imagine to benefit, performance-wise, I'll need to configure it specially.

What are the primary ways I can take advantage of my no UPDATE/DELETE guarantee?

I believe I could all-but-disable vacuum, but I'm not 100% certain of that and I don't think that's a huge win anyway. What are the largest configuration changes that could be made here?

If it matters, writes happen at a specific time once-a-day and never at other times. However, I think that detail is getting into "too localized" territory so I'll edit it out of this question if it isn't helpful. (It if is helpful, I'll edit out the disclaimer portion here!)


texts: Issue with connecting to the mysql database

texts:
We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database
    If an agent does a  refresh of the monitor they will be logged out.
    This is the error we get when the vcc web server starts.
    [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }
    I have done below step,but this issue still exsit.Please check.
    old_passwords set to off
    ALTER TABLE agent_login_session CHANGE operatorId operatorId VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL – done

·         info: socket.io started
###### Session SB Server connected fail! ######
{ [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }

1. update mysql to use long password()
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0

2. ALTER TABLE `agent_login_session` CHANGE `operatorId` `operatorId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

Please help me here.


texts: What configurations affect the speed of DDL statements in PostgreSQL

texts:
I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible.
This is for a development environment where data integrity is not important.
This database reset will happen very often.

What are the configuration options that might help with this (or psql meta commands that can help speed this process up)

EDIT:
Imagine i have a single sql file (that i constantly edit and save) that creates everything needed in an empty database.

BEGIN;
CREATE TABLE ...
INSERT INTO ...
CREATE TABLE ...
INSERT INTO ...
COMMIT;

I need it to run as fast as possible, right now it takes a few seconds even for about 10 tables, i would like it to be under 1 sec


texts: Design Pattern to Reduce Number Of Columns

texts:
I have a table with 49 columns, which is already normalized.  It seems kind of excessive, but since every field is part of a form and the main entity is the form there are no relations I can refactor to delegate columns to another table, so hence my question "Are the any patterns that target the reduction of the number of columns in a table?"

I've tried to do something like a Question - Answer schema, but since the Answer value can be of many different Data Types (Enums, Strings, Numbers), there is no generic way to parse everything.

UPDATE:

I hadn't considered but the data will be eventually serialized on JSON to be served from a WEBApi so having a lot of nested fields could mean trouble.


texts: Which columns to index with LEFT JOIN and GROUP_CONCAT

texts:
So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching:

        SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, supplier_company,
        GROUP_CONCAT(wp_terms.slug SEPARATOR ', ') AS allslug,
        GROUP_CONCAT(wp_terms.name SEPARATOR ', ') AS allcatname
        FROM wp_posts AS wpp
        LEFT JOIN wp_term_relationships ON wpp.ID = object_id
        LEFT JOIN wp_terms ON term_taxonomy_id = wp_terms.term_id
        LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

        WHERE post_type = 'post'
        GROUP BY wpp.ID

1) How to speed up the group_concat?
2) Should I index the column for ON at my LEFT JOINs? Like: wpp.ID and object_id?


texts: PostgreSQL 8.4 no levenshtein fuzzymatching?

texts: I'm using debian squeeze package, it says function does not exist?


texts:
I have taken a backup of mysql instance like this

innobackupex \
   --user=$MYUSER \
   --password=$MYPASS \
   --no-timestamp \
   --parallel=$DUMP_THREADS \
   --stream=xbstream \
   --slave-info \
   --extra-lsndir=$LSN_DIR \
   --tmpdir=$TMP_DIR \
   --no-lock \
   --safe-slave-backup

I am trying to restore this backup like this.

zcat backup_file.xbs.gz 2>/dev/null | xbstream -x -C /var/lib/mysql/

I am getting this following error.

xb_stream_read_chunk(): wrong chunk magic at offset 0x0.

I have tried all google suggestions, upgraded xtrabackup to the latest version.

Nothing seems to be working.


texts: Master slave replication with different mysql version is on both slave and master

texts:
Currently, we have MySQL master-slave replication. Both master and slave servers have Mysql 5.5 Currently I need to add one more Master server having version 5.7.

Is it possible to add Multiple masters to a single slave server?

  One master and slave is having Mysql 5.5 and new Mysql server is 5.7

Is it possible to configure Master-slave replication with different MySQL version?


texts: Ideal and Efficient Way of Handling User Logins with Multiple Types (i.e. Employee, Client, etc.)?

texts:
I'm developing a database where I will have two types of users:  Employee and Client.  I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to distinguish whether the user is an Employee or a Client.  This is to make it more convenient when a user logs-in where it will only check one table:  USERS TABLE for username and password

However, I realized that this means I would be losing the opportunity to have separate ID's as PK for Employees and Clients (employee_ID and client_ID).

Would having another table called EMPLOYEE TABLE and CLIENT TABLE (that have user_ID_FK as FK, employee_ID or client_ID respectively) be ideal or would that de-normalize the tables?

How best to handle user log-in where there are multiple different types of users (Employee, Client, etc.)?


texts: How do indices impact query performance?

texts:
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the query performance improve in any case with an index added (of course for queries using the index at all) or is it even possible that the query performance will degrade with too many indices because it becomes necessary to consult all the indices to get the result?

In case there are different indices on a table: will they all be considered, or only the best from the optimizer point of view? Does Oracle implement multi-dimensional indices?


texts:
I know to check for live deadlocks

select * from sys.sysprocesses where blocked>0

However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? Note: I want to be able to do this without having to configure on any trace etc - just get the information from a system table. Is that possible?

Thanks.


texts: Trying to set up new data_directory on Postgres 9.3

texts:
Ubuntu 14.04, recently installed Postgres 9.3.

Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that.

However, I get this error when trying to restart the server:

user@server:/etc/postgresql/9.3/main$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server
 * Error: pid file is invalid, please manually kill the stale server process.

I don't have too much experience setting up Postgres, so I'm not sure what to do from here, and Googling around provides no quick answer.


texts: What Problems Does an Eager Spool Indicate

texts:
Currently running on SQL Server 2008 R2

I am attempting to increase performance of an UPDATE statement.  I notice an Eager Spool operation in the showplan popping up.  My understanding of spooling operations is pretty basic - they create temporary storage for the table during the update.

I also know that, while they are preventing much worse execution times, eager spools are often indicative of underlying problems with table structure and/or query statements.

My question is pretty simple:  When you see an Eager Spool in your query plan, what problems do you first look to address?

I will be analyzing every part of our system to increase performance - I'm just looking for guidance as to where I should start.


texts: Has anyone successfully made a connection to SQL Server using a COBOL program? Hoping to make a connection so that hundreds of COBOL programs don't need to be rewritten.


texts: MySQL: How to match data with regular expression string

texts:
I have a postcode column in my table with values like AB45*, RD4*, ADB567 etc.
Now I want to match my data with this column. The * means that a string starting with AB45 should match with this column. If I use AB45 45D it should fetch a row containing AB45*
If I use RD489 on my field, it should match with RD4* column value.
What can I use to achieve this requirement?


texts: Multiple Oracle Instances --  is this a good practice?

texts:
One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're experiencing performance issues.

I have no access to other instances or the machine, and all the tools I have are alert.log, AWR, and ADDM. I know there's something related to multiple instances, but I cannot prove it.

So, my question is, have you experienced a similar situation? How should I deal with it?
How can I identify the cause of performance issues related to multiple instances?


texts: MYSQL Server is getting restarted multiple times every 2-3 days

texts:
Mysql Server is getting restarted saying below errors. It will be grateful if someone can help me to solve this.

2017-10-04 17:07:43 3402 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048548 bytes)
2017-10-04 17:07:44 3402 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048548 bytes)
2017-10-04 17:07:46 3402 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048548 bytes)
2017-10-04 17:07:48 3402 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048548 bytes)
2017-10-04 17:07:49 3402 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1048548 bytes)
2017-10-04 17:07:51 3402 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1065016.

2017-10-04 17:07:51 993b90 InnoDB: Assertion failure in thread 10042256 in file ha_innodb.cc line 17352

Using 32bit centos system as database server.

Tried increasing max connections, innodb_buffer_size but did not help.


texts:
I'm trying to create a logon trigger that will restrict users views so that one user can select/ view jobs relating to them and only to them within the JOB table.

Additionally these new users must only have the ability to select the job table whereas users who have a REG_DATE > 1 year for instance will be able to edit job details within the JOB TABLE.

I don't know what the trigger should look like hence the reason i'm posting,  also is a trigger ideal for this instance or should look towards using grants?

-----Below are my tables

    CREATE TABLE  tblss_user(
    user_id       INTEGER NOT NULL,
    user_name     VARCHAR(15),
    reg_date      DATE

user_id = pk

    CREATE TABLE job(
    job_id      INTEGER NOT NULL,
    job_name    VARCHAR(25),
    job_salary  INTEGER NOT NULL,
    fkuser_id   INTEGER NOT NULL

jobid = pk

fkuser_id = fk

thanks for your time!


texts: How to copy a table during replication in MySQL?

texts:
I have two MySQL servers; db1 and db2. db1 is production and db2 is for reporting. I have setup replication where db1 is master and db2 is slave. This current setup works fine.

I was given a new requirement to not replicate a field from the USER table because it contains sensitive information.

Here are the steps I implemented:

I created a copy of USER, called USER_CLONE that does not have the field to be omitted
I set up a stored proc (UPDATE_USER_CLONE) to DELETE FROM USER_CLONE and INSERT INTO USER_CLONE SELECT  FROM USER
I added the following to my.cnf on db2:
  replicate-ignore-table = USER
  replicate-ignore-table = UPDATE_USER_CLONE

When replication starts, I get an error on the INSERT statement because USER does not exist on db2. I understand the error since replication will rerun all the db statements.

What is the right way to set this up so USER is ignored or has a field ignored, or USER_CLONE can be setup to replicate?

Thanks for any advice.


texts: Can I install an additional oracle database on one node of an oracle cluster (RAC)?

texts:
Let's say I have a two-node Oracle RAC environment. Can I install an additional Oracle database on one of the node?

Thanks.


texts: Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)

texts:
Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries.

Our database version is oracle 11g r2 enterprise edition(partition option added). The concerned two large tables are: incident and incident_area_info(one incident corresponds to multiple incident_area_info, and they're joined using incident_id). So I want to use the following strategy:

Split incident table into two partitions: closed_incidents, active_incidents, using list-partition and status as partition key. Also, I manually enabled row movements on incident table and incident_area_info table, so that I can close the incident.
Split incident_area_info using reference partition.
Drop the original indexes and replace them with corresponding partitioned local indexes
Search open incidents only by default

I have applied this strategy in my developing environment and the search operation's execution time reduces to roughly 10% of the original on average(we have nearly 4 million closed incidents and only about 40,000 active incidents).

But "row movements" is disabled by default, so maybe enabling it will cause some performance problems.  Well, of course, when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly. And rowid will be modified after a row is moved(I'm quite sure we do not use rowid in our system, so this won't be a problem).

Question1:

Besides those mentioned above, will there be any other bad side effects when enabling row movements?

Question2:

I suspect that moving rows will create space holes in the original partition and the data file will be fragmented after long-term running. Is this true?

Question3

If question2 is true, then is there a way to remove these space holes, like alter table mytable shrink space;

Question4

Here one guy said 'everybody should be carefull when enabling row movement in production system since enabling row movement invalidates all dependent views, which could result into plenty invalidate objects', but in my developing environment, after moving rows in incident table, the materialized view counting on incident table still works, and in dba_mviews everything seems to be fine. So, did I misunderstand what he means?

Any suggestion is greatly appreciated.


texts: Clustered Index and PK - Two different Objects?

texts:
I have a table which has a Clustered PK as follows:

CREATE TABLE [dbo].[PanelCcmRawData] ADD  CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED
(
    [CcmRawDataId] ASC,
    [LineageId] ASC
)

I understand that when you create a Clustered Index, the table itself is the clustered index. However, when you make the Clustered Index the PK as well, it seems SQL Server creates an actual index file. I noticed this because when I ran a query to see all the indexes on my table, the number of seeks on the index and the size etc., I saw an entry for PK_CcmRawData with a size of 331GB.

I was a bit surprised to see this. I'm curious why, when creating a Clustered Index that is also the PK, SQL Server creates an index file. I would have thought the table itself would act as the Clustered Index and PK. So there appears to be something I'm not understanding.


texts: Can we have more than one arbiter in monogodb replica set

texts: I am setting up cluster of my webserver which uses mongodb. I am doing replica set of mongodb.I have only two machines. So can I have two arbiters in replica set ?


texts: Preserving timezones explicitly in postgres

texts:
I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather persuade you to store dates as timestamp with time zone and never as just timestamp. I'm especially struggling with daylight saving times.

My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. Users write posts that get stored along with timestamps for creation and updates.

Let's say a user publishes a post on 2020-01-01T10:00:00+01. If a user now reads that post on the same day it should display posted on 1st of January at 10 am. If that same article gets clicked in Berlin in July it should still say posted on 1st of January at 10 am regardless of the DST.

My intuition is now to store the time as a timestamp without time zone because otherwise Postgres would convert that time to UTC and store it that way. Later I wouldn't be able to refer to the actual timezone that post was published in. In that case, it would read posted on 1st of January at 11 am (due to Berlin now beeing ahead two hours) which could confuse the author if they were to check the time they initially published the post.

Are my thoughts correct and did I found one of the corner cases where to not use timestamp with time zone or am I missing something crucial here?


texts: How long does it take for Oracle Flashback Database to restore the data? Is it an instantaneous operation or dependent on the data volume?


texts: Alter table and update in sql server transactions

texts:
I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a select * to check that everything is ok and then make a rollback of the entire thing.

Something like

declare @transactionNameA varchar(20);
declare @transactionNameB varchar(20);
select @transactionNameA = 'TransactionA';
select @transactionNameB = 'TransactionB';

begin transaction @transactionNameA
alter table A add Column int;

begin transaction @transactionNameB
update A set Column = 1 where ID=1;
select * from A;

rollback transaction @transactionNameB;
rollback transaction @transactionNameA;

When I write that code I get an error saying the the column doesn't exist

What am I doing wrong?
Thanks for your time and help.


texts:
I'm having a hard time modeling an activity feed that should show events based on user roles. I'm using Postgresql 9.4.

I have a projects table. Each project has many users through a memberships table. The memberships table has a role column that stores a user's role in a given project. Standard stuff.

memberships
-------------------------------------
| id | user_id | project_id | role  |
|  1 |       1 |          5 | admin |
|  2 |       2 |          5 | user  |

I have a buckets, uploads, and documents tables. A bucket is essentially a folder, and it has many uploads, and an upload has many documents. A bucket has many documents through the uploads table.

buckets
-----------------------
| id | name  | public |
|  1 | Stuff | true   |

I need to track activities on memberships, buckets, uploads, and documents. This is fairly easy. I'm planning to use a polymorphic association. In an activities table I'll have these columns.

activities
----------------------------------------------------------------------
| id | user_id | project_id | action | trackable_id | trackable_type |
|  1 |       1 |          5 | create |            3 | Upload         |

Trackable id and type store the record id and table name of the trackable object, in this case an upload. This lets me easily retrieve, in a single query, a user's activities, and a project's activities, and render view templates based on the trackable object type, and the action type.

Problem

My problems start with buckets. Any activity on private buckets should only be visible to admins. This includes activity on descendents of private buckets: uploads, and documents that belong to those uploads.

Solution 1

The easiest way I can think of is to add a private column to the activities table. The lets me do something like this (this is Ruby pseudo-code, specifically Rails, but logic stands):

project = Project.find(1)
activities = if project.admins.include?(current_user)
  project.activities.all
else
  project.activities.public
end

This is not scalable though. If we add a table to track access to folders, and change permissions so that they go from:

  Admins see all activity, users see public bucket activity, activity on their uploads and documents that belong to public buckets, and membership activity.

To something like

  Admins see all activity, users see public bucket activity like above, and in addition they see activity that belongs to private buckets they can access.

Then the private column becomes meaningless in the activities table.

Solution 2

Another way is to add a bucket_id column to the activities table, and separate activities by ones that belong to projects or buckets.

activities
---------------------------------------------
| id | user_id | project_id | bucket_id | ...
|  1 |       1 |          5 |         3 | ...

For non admin users I can then run two queries. One for project activities, and one for bucket activities that are either public, or in the future, on buckets that the users have access to.

I don't like this approach because it's also not scalable. If we add a tasks table where tasks can also be restricted by access, we would have to add another column to the activities table, task_id, and so on.

Approach 3

Create a table for each activity type. Much like approach 2, but with normalized data. I still don't feel comfortable creating what essentially is three different tables with almost identical data.

Any ideas on the best way to approach this problem?


texts: How can I share values in maintenance plan tasks?

texts:
A simple example would be a maintenance plan that clears a directory of old files and create some copy backups of all databases on that instance to that same directory. This is run daily

The first cleanup step contains the path where the files are deleted. The second step actually creates the copy backups in the same directory as the first step. So any changes made to the path have to be done in multiple places. For a simple 2 step maintenance plan this is not really a big issue but could be for more complicated plans.

So is there a way that plan steps can share information? I don't necessarily mean passing information between steps but perhaps a global space of the steps?

I could not see anything in the toolbox that would work for this. Best I got is that I could add two new steps.

Create a temp table with the path (and any other constants.)
Using that path delete directory contents
Run copy-backups
Delete temp table

I had thought this question: Can I pass data between stages in a maintenance task would have helped but it was focused on sending completion information in email.


texts:
I have a Stored Procedure where I pass a value of 0,1,2.
0= no where
1= Duration >0
2= Duration =0

  select * from Table where case  when @duration =1 then Duration else 1
  end >0

this works however i can't get the other values to work. I've tried 3 case statements but only the last one works.

How can I get a dynamic where statement to work for 3 values?


texts: Need help with a table relationships and over-all design

texts:
My design is a Netflix clone which has movies and shows just like Netflix. There is also a table called transcodes which will have information on a movie or episode video transcode.

My problem is, I don't know how the I should connect the transcodes table to the movies and episodes table. I thought about having a movie_id and episode_id foreign key in transcodes but then I was struggling with coming up with the query. I think adding a transcode_id to the movies and episodes tables could work, but I feel there is a more efficient way of handling it.

What would be the best way to connect transcodes to the movies and episodes tables?

Maybe my design is bad? If anyone sees any possible improvements, I would appreciate feedback on that as-well.

- EDIT -

I've updated my design to reflect the feedback. The only question I have is, some tables have both movie_id and show_id which means one of those will always be NULL, is that bad design? Or is that acceptable? Also, take a look at the titles_posters table. Something seems a little off about how everything is associated, seems like there will be quite a bit of nulls as-well, but maybe that is fine?


texts: Update SQL n:n table with multiple associations

texts:
I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here.

I have 3 tables (details left out for clarity) :

inv_items

id bigserial (PK)
sku character varying(24)
name character varying(32)
...

inv_item_groups

id bigserial (PK)
name cahracter varying(32)
...

inv_item_group_members

item_group_id bigint (FK -> inv_item_groups)
item_id bigint (FK -> inv_items)

Now, in my code, I have an object like so (in pseudo-code)

class ItemGroup
   id:long
   groupName:String
   items:long[]

and these objects can be modified, then needs to be updated. Since I want to preserve the key integrity, I need the inv_item_group_members table (otherwise, I would've used other solutions).

Now, the usual way I was doing this was to

DELETE FROM inv_item_group_members WHERE item_group_id = $1
-- where $1 is the object's id

the, for each items in the object

INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)

Is there a better solution? What are the alternative? I'm thinking of a SQL function, but not really sure what's the best approach here (I'm not very experienced with PGSQL, yet.) I have read about writable CTE, but it does not address the case when elements are removed from the array (i.e. association removed).