Assign root user password after installation I have installed MySQL 5.7.9 on RHEL 6.5. After installation, when I tried to enter using system root password it is not able to login. I know I have to assign the password for MySQL root user but how can I assign the password when MySQL is not even letting me enter?

When I run the command:

#mysql -u root

It just kicks me out with this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I know after MySQL installation it generates the temporary password in /var/log/mysqld.log. But after looking into this file I found nothing except these errors:

0 [ERROR] Incorrect definition of table mysql.db:
          expected column 'User' at position 2 to have type char(32),
          found type char(16).
0 [ERROR] mysql.user has no `Event_priv` column at position 28
0 [ERROR] Event Scheduler: An error occurred when initializing system tables.
          Disabling the Event Scheduler.
0 [Note] /usr/sbin/mysqld: ready for connections.
         Version: '5.7.11' socket: '/var/lib/mysql/mysql.sock' port: 3306
         MySQL Community Server (GPL)
2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
5 [Note] Access denied for user 'root'@'localhost' (using password: YES)

The problem is with incompatible data files created by a different version installed in the past. The errors show the old datadir was left from an old installation that is not compatible with 5.7.

If your database does not contain any useful data yet, simplest way to fix it is to clear the datadir and let the new version initialize it fresh.

Uninstall 5.7, purge the datadir, and then reinstall. Normal uninstall won't clean it, gimp does not delete your images on uninstall either. You need to delete and reinitialize the datadir, not the application packages. It might be at /var/lib/mysql/ (the default).

If you already have some data in the database you want to keep, you should instead follow the instructions for upgrades.


Create a view that dynamically UNIONs new tables I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number).

All the tables have the exact same structure (both column names and types). I want to create a view to UNION ALL them together. In addition there will be new tables of the same type that will be added later into the database, so I need the view to be able to 'run through' all existing tables in the database and add them (either adding or creating the view from scratch).

I've created a simple view with the tables name's hard coded but I'm having difficulty how to approach the issue of having the view created dynamically.
I'm using SQL Server 2012.

The tables being added are the result of an outside script running against an API per account so I can't avoid having multiple tables in the database. I assumed I'll probably have to write some script code that would actually run and check for new table names but I wanted to see some example to figure out how to do that.

You can prepare the SQL statement using a cursor to build the SQL string, and execute it using sp_executesql.

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR
    select name
    from sys.tables
    where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

You can use text mode and see the query that it is building, thanks to the print statement near the end.


Foreign key with multiple references I have the following three tables in mysql database named "THE_COLLEGE"

mysql> desc students;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STU_Id   | char(5)     | NO   | PRI |         |       |
| STU_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc staff;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STF_Id   | char(5)     | NO   | PRI |         |       |
| STF_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc users;

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Id         | char(5)     | NO   |     |         |       |
| user_Name  | varchar(20) | YES  |     | NULL    |       |
| Password   | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

If a new row want to be inserted into the table "users", that row must be found in students table or staff table.

Which means a staff or a student can be a user.

Is it possible?

I tried with following foreign keys.

ALTER TABLE users
ADD CONSTRAINT fky_stu
   FOREIGN KEY (ID)
   REFERENCES Students (STU_Id);

ALTER TABLE users
ADD CONSTRAINT fky_stf
   FOREIGN KEY (ID)
   REFERENCES Staff (STF_Id);

But the user table is allow to insert a row, if the row found in both tables (Student and Staff).

But I need to allow if the row found in any of the table (Student or Staff).

The comment from @ypercube is right.

You shall model an intermediary table to be able to enforce FKs:

A less formal model, in which FK enforcement would not be possible, could be:


What is a good way to parameterize a materialized view on Postgres? Given for example a materialized view like this (Postgres 10.3):

create materialized view my_view as
    select * from my_table where sell_date < '2018-03-01';

The sell_date comparison value ('2018-03-01') can change sometime, but I want to avoid to drop and recreate the materialized view each time. The only idea I came up with it is using an external table with some metadata values like the date needed:

create materialized view my_view as
    select * from my_table where sell_date <
        (select original_sell_date from some_metadata_table);

Is there any other way to work around this limitation of materialized views on Postgres?

One of the problems with my current solution is that you can have two or more materialized views that use the same value, but they may need at some point of different values. In this case a duplication of the metadata table is needed.

There are three ways,

Use foreign data in a table that holds the inputs.
Make sell_date a function of the current time. Such as AND sell_date > current_time - '5 days'.
If nothing depends on it, drop and reload a new ad-hoc MATERIALIZED VIEW by the same name in a transaction.


AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason We have been using Aurora serverless databases in staging and production environments for a few months now, and find the CPU remains in the range 15-20% almost all the time. However in the past week the CPU utilisation has been growing, even during periods of zero load and is now at 38%. I'm worried this trend will continue further and begin to become an issue.

I saw the same trend in our Staging environment, but it only lasted 3 days and then returned to the usual level.

What could be driving this and how should I be debugging and resolving the root cause(s)?

SHOW FULL PROCESSLIST

Returns only 6 rows.

----- Update 4th Sept 2019 -----

In the end, I just continued to monitor it, and once it reached 50% it dropped back down. I guess it is a quirk of Aurora serverless? Would be great to hear from anyone else facing this issue as I hate not understanding things.

What solved it for me was setting the min and max instance count higher than before (forcing aurora to scale up, for example from min 2 ACU to min 4 ACU), and after scaling restoring the scaling settings.

I used 'Force scaling the capacity to the specified values when the timeout is reached' and 'Apply immediately'.

It also solved another issue we had, the 'Serverless database capacity' indicated 4 ACU while it was actually 2 ACU (as seen in the picture)


Best database choice for a highly scalable web ticketing system? Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will spike with large numbers of reads (users visiting the site, up to 1-2 million per day) and a low-ish level of writes (write when a user requests a ticket, and when they buy it).

The writes need to persisted to disk, but we are keen on keeping the whole database in memory (perhaps using memcached). Right now we are going to build this on Mysql (activerecord on RoR).

Question is, will this setup be scalable for our future needs where we may need to organize dozens of events with 10s of millions of hits.

Scalability is factor for every business that expects large growth. Ultimately only you and your team can answer the question with whatever is right for your pocketbook and time considerations. The best we can do is offer some options.

You could use a master MySQL database for the writes and then implement replication to create multiple, read-only slaves.

Or MySQL for the write database and then create a custom ETL application to convert the transactional data into read-only and place it into a NoSQL database like Cassandra. I've found Cassandra to be optimal for tons and tons of reads (which is great if people are performing searches on your content), but it will require you to be pretty savvy in the database realm.


Alter stored procedure - linked server not connected I'm trying to change a stored procedure that is used to updates data in a local and a linked server. "UPDATE [PcName].[DatabaseName].[dbo].[TableName]"

As both servers are available the stored procedure can be executed (updated) without an problem. If the linkedserver is down, I'm getting this error message:

OLE DB provider "SQLNCLI11" for linked server " linkedserver " returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "linkedserver " returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

How can I still change a stored procedure while the linkedserver is down?

You 'could' temporarily change the linked server 'connection' to point to a Sql instance that is available, but be aware that linked servers do not 'honor' Deferred Name Resolution and any object referenced by the linked server will need to exist on the temporary connection.  Here is someone with a 'similar' problem and some suggestions.


replication monitor: why is current average performance poor? I am a replication rookie.  We have a very simple transactional replication set up in SQL Server.  I am looking at the Replication Monitor on the subscriber and one of my subscriptions show Poor Current Average Performance.  My understanding is this is derived from the latency threshold but when I look at the detail of the subscription, Performance is Excellent and Latency is 00:00:00.  For what it's worth, Synchronization  Status is fine.

Why is this current average performance poor?  Over what time period is this "average"?  This subscription has had no noticeable issues and has been active for some time.

(I am asking for my own knowledge as well as ensuring there is not something broken/sub-optimal that should be modified)

Personally I ignore the excellent, poor, etc.  As long as the number of records pending isn't very high, and the amount of time to catch up isn't very high I consider everything is fine.  That front screen monitor is pretty misleading most of the time.


What is isolation level of autocommit? I'm trying to make sense from "autocommit" concept.
Originally I've heart about it in the context of Java JDBC API Connection.setAutoCommit(boolean autoCommit)
Quick search revealed that the term is not specific to JDBC, PostgreSQL refer to "autocommit" too:
https://www.postgresql.org/docs/current/sql-start-transaction.html

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called “autocommit”. Other relational database systems might offer an autocommit feature as a convenience.

If it is widespread what is isolation level of autocommit mode?
Conceptually as we have only one SQL statement before implicit COMMIT isolation levels almost make no sense for autocommit. Only READ UNCOMMITTED might influence the result, other anomalies are defined in term of a sequence of SQL statements.
Can I alter autocommit isolation level to / away from READ UNCOMMITTED?

The default isolation level is controlled through default_transaction_isolation and is read committed in a standard installation.
You can check your current setting using:
show default_transaction_isolation;

As Postgres does not support (or implement) read uncommitted there is no need to change this "away from read uncommitted".
If you want to change the isolation level to "higher" one, even in autocommit mode, you can use:
set default_transaction_isolation = serializable;

The above will change the default for the current session (=connection).


Periodic snapshot fact table with monthly grain - Question on dimensions I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.

My end goal is to be able to do a headcount of employees at the end of the month, therefore I designed my fact table as a monthly snapshot where each end-of-month I will populate my fact table (one row per employee per month).

My dimension table includes a DimEmployee table which is an SCD2 (any change made to an employee's information causes a new row to be introduced in the data source and I am keeping track of the most recent employee information via an is_current flag set to 'Y' or 'N').

My question is: do I need to update the dimensions daily and only insert whatever data I have at end-of-month into the fact table? Or do I update the dimensions end-of-month as well?

Thank you in advance!

You could go either in generally assuming you want to follow best practices of conformed dimensions you would want to update the customer daily so that other fact tables would have the updated information. what you don't want to do is have your previous snapshots updated to point to latest member as facts they are immutable and they should always point to the record as of the snapshot period


How to Set the Transaction Isolation level in a Table Value Multi Function How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure.

create function dbo.Test()
RETURNS @TestTable Table (test varchar(1000))
AS
begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I am receiving error below:

Msg 443, Level 16, State 15, Procedure abcde, Line 12
Invalid use of a side-effecting operator 'SET TRANSACTION ISOLATION LEVEL' within a function.

You can't set the transaction isolation level inside a function.  You can use lock hints on the queries used in the functions instead.

Although you should really avoid using either READ UNCOMMITTED or the nolock lock hint.


Recover ancient versions of postgresql I was running different postgresql versions. And accidentally they were removed. they contained several databases and I want to recover them. I'm wondering if there'a a command line in linux that allows me to recover lost data.

Your help would be so precious!

Thank you in advance

Take a backup of the entire data directory for each.
Reinstall the version of postgresql you need
Point its data directory at your existing one

If you are running a packaged version of postgresql then there might be nothing to do for 3


Avoid using Many to Many Relationship I have a 3 tables, Employee, Languages, Employee Languages.

Now, Employee table is a profile table. Nothing special(Id, first name, last name etc.).

Languages table has two columns: Language ID column and Language name column. This table, basically is a list of all languages.

Employee Languages consists of three columns: Employee ID, Language ID and Proficiency.

So, when I want to show employee languages, I have to get language id, then correlate that id to language name and then show it to the end user.

I'm currently thinking about new scenario with two tables:

First would be Employee table.

Second would be Employee Languages, which consists of Employee ID, Language Name and Proficiency.

So, Language name and Proficiency would be populated in front end and would be shown as a drop down list.

My question is which scenario would be much more effective in my case?

Your 2 table design approach can be implemented using a view. This would not compromise your underlying database design based on 3 tables.

Views are under used but often give the best of all worlds.

Expanded:

From your "normalised" 3 table design, which looks OK, you will CREATE VIEW (SELECT Employee ID, Language Name, Proficiency FROM Employee, Languages, Employee_Languages WHERE etc. ). Your application then selects from the view.


Index Design going incorrect We have a table called Feedback as follows :

id            - int(10) unsigned    NO  PRI     auto_increment
vendor_Id     - int(10) unsigned    YES MUL
created_At    - datetime CURRENT_TIMESTAMP
updated_At    - datetime(3)
code          - int(11) YES
feedback      - varchar(300)    YES

CREATE TABLE `Feedback` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_At` datetime(3) NOT NULL,
  `uniqueKey` varchar(255) DEFAULT NULL,
  `customerContactNumber` varchar(15) DEFAULT NULL,
  `customerName` varchar(30) DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  `feedback` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `feedback_unique` (`uniqueKey `),
  KEY `codeIndex` (`vendor_Id`,`updated_At`,`code`),
  KEY `feedbackIndex` (`vendor_Id`,`code`,`feedback`,`updated_At`),
  CONSTRAINT `feedback_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and we need to create index for our Feedback Table.

So we added 3 indexes based on fields as part of where clause :

 index1 (vendor_Id, updated_At);
 codeIndex (vendor_Id, updated_At, code)
 feedbackIndex(vendor_Id, code, feedback, updated_At)

Now when we run the following query :

explain  select  'id', 'customerName', 'customerContactNumber', 'feedback', 'updated_At'
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  code >=0
      and  code <= 6
    order by  updated_At DESC
    limit  10

the mysql output says :

possible_keys : index1,codeIndex,feedbackIndex
keys : feedbackIndex

Am curious to know why the explain statement shows 'feedbackIndex' as the key selected. We were expecting it to select 'codeIndex'

There are other 2 queries :

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
    group by  code

===> shows 'codeIndex' as key in output of explain statement. which is as  expected

explain  select  code, count(*) as count
    from  Feedback
    where  vendor_Id = 1
      and  updated_At <  '2017-11-15 12:58:12.387'
      and  updated_At >= '2017-11-01 00:00:00.000'
      and  code is not null
      and  feedback is not null
    group by  code`

===> shows 'feedbackIndex' as key in output of explain statement. which is as  expected

I can not figure out why our first query is showing feedbackIndex as the keys.

When creating an optimal index, start with = -- vendor_id in your case.

Then you get one crack at a "range" -- updated_at or (in the first case) code.

Also (or alternatively) "covering" the query can be beneficial.  This is where all the columns mentioned in the SELECT exist in the INDEX.  In the case of SELECT *, that is all columns in the table, and that is usually to many.  When doing code, count(*), only 3 or 4 columns are mentioned, so "covering" may be practical and beneficial.

The PRIMARY KEY (id) is included implicitly in any secondary key.

It is handy to provide the entire EXPLAIN when asking questions like these.  It is instructive to discuss "key_len" and how that tells you how many columns it used.  EXPLAIN FORMAT=JSON SELECT ... is even better, since it is explicit.

Why did it pick (vendor_Id, code, feedback, updated_At, id) for the first query?  It is missing created_at, so it is not "covering".  Probably it decided that vendor_id (=) plus code (range) was slightly better than vendor_id, updated_At.  I would expect the latter (aka "Index1") to be better because it might be able to consume the ORDER BY.

index1 (vendor_Id, updated_At) is redundant (and can be DROPped) because of codeIndex (vendor_Id, updated_At, code).

The second query needs (vendor_Id, updated_At, code) -- =, "range", "covering".  The query can be performed in the index, without touching the data.

The third query adds feedback; so it needs the columns of (vendor_Id, code, feedback, updated_At), but it is hard to know whether that order is best or (vendor_Id, updated_At, code, feedback).

What version are you using?  5.6, 5.7, and 8.0 have each made significant changes to optimizations in this area.

BTW, and  code is not null is redundant in the first query.

The first query cannot stop after 10 rows; this is because there is more than one "range" in the WHERE.


Optionally exclude certain partitions using OLA's scripts I have been using OLA's scripts for a long time and love them.

I have a very large database - 20+ TB with hundreds of partitioned tables.

I purge old data by dropping (merging the old partitions).

It is partitioned by date with weekly partitions going back about 6 months.  I have to run daily index maintenance by partition to have any hope of keeping caught up (actually never catch up).

I am now on SQL 2017 and am trying ONLINE Rebuilds by Partition using the Resumable option.

What I find is every day I am rebuilding the current partition in almost every table only as it is always fragmented and likely will be fragmented tomorrow again due to massive # of inserts.

What I would like to do is exclude this partition.

This is the partition the current date falls in.

Ie, I would like to only rebuild past partitions.

Having this as an option would be very helpful.

Having this as an option would be very helpful.

Ola's scripts are now on github and a Feature request is already there.


Is it possible to change the SQL Server errorlog file number on Linux? Everything is in the title. We can change the number of errorlog files using the registry on Windows, does anybody know if it is possible on Linux? I see no mssql-conf option for this.

Thanks

This is not possible at the moment, even on CTP 3.1 of SQL Server 2019 as I write this.
As you've discovered, it requires changing a registry setting, and trying to run xp_instance_regwrite results in the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Bob Ward's book Pro SQL Server on Linux doesn't mention anything either. It refers to filelocation.errorlogfile for setting the path using mssql-conf, but not the number of files.
If I were to hazard a guess, this will eventually find its way into the product, but it is not there now.


Non clustered index leaf level When creating a non-clustered index, the leaf level will contain a reference to rows in the original table:

if the table has clustered index, the leaf level will contain the clustered index key;
if the table has no clustered index (a heap), the leaf level will contain  a pointer to the physical address of the row in the table;

My question is why the non-clustered index wasn't designed to contain the physical address in both cases (in addition to clustered index key). It could save the reads of the B-Tree for key lookups when clustered index is defined.

A reason the physical row locator is not stored in the non-clustered index leaf nodes is because the value would need to be updated whenever the physical row location changes due to page splits, reorgs, etc. Having only the CI key as the unique row locator on non-clustered indexes avoids this maintenance, which would be especially costly on a table with many non-clustered indexes.

Note that this isn't as much of a consideration with heaps because those do not suffer from page splits, although forwarding pointers is a consideration when rows are updated to a larger size.


Making sense of these business rules My scenario is a shop/warehouse and I have a Product table which contains basic product details including it's name, price, quantity on hand etc. I have a Customer table that has the shops customers and their details. And then I have a table that is confusing me: the Despatch table. The business rules for the Despatch table are as follows:

  Its purpose is to assist the warehouse staff in quickly locating
  products that are needed for the daily delivery run to customers. It
  contains for each product the number of units needed for delivery. The
  warehouse shelf location is also stored to facilitate quick retrieval
  of the product. Thus the table stores product name, shelf location and
  the number of units required for dispatch. Note that the product name
  is stored instead of a product code as the name is much more
  meaningful to the warehouse staff than a cryptic code. In order to
  optimise performance the table is preloaded with the product name and
  shelf location for each product.

Dispatch table

Disp_id     Number(4)
Prod_name   Varchar2(30)
ShelfLoc    Varchar2(6)
Quantity    Number(5)

Then finally I have a Sales table which holds all the sales transactions in the shop and is the bridge table between the Product, Customer and Dispatch table.

My confusion with the Dispatch table is that logically it seems like it would behave as though there's a one-to-many relationship between Product and Dispatch tables, but the business rules make it seem like it's a one-to-one as it asks me to preload all the products even if there are no deliveries/dispatches to be done? I guess I don't fully understand the concept of 'dispatch'.

Presumably the Disp_id is a non-unique field as you'll have an order table which contains the customer name / delivery address etc

so you'll have an order has 1-+ dispatches
The product table will cant be a 1-1 relationship with dispatch otherwise only one person would ever be able to order that product regardless of how many they order so the product -> dispatch needs to be 1-+

looking at the information here this is less a table you want to create and more a View since all that information will be stored in other locations you dont want to duplicate things, you just want to derive the locations

A view based on the

[Orders]-> OrderID (Is the dispatch ID)
[Orders]-> Product ID -join-> [Products] -> ProductName
[Orders]-> Product ID -join-> [Products] -> Shelf Location
[Orders]-> ProductQuantity

(I used to work in a warehouse and thats how it used to work there and makes sense, and fuflls all the needs that I can see you wanting)

There's no reason to load all of the products, you want to just load the products of an order

Think of the Dispatch as part of the Full order, the warehouse staff only needs to know that this gets picked up from there and gets placed with other items of this dispatch ready to actually be dispatched

Hope that helps (it is based on my understanding of what I've read)
Ste


No, but given the function is read-only, one can try running it again in the concurrent session and see if the slowdown is reproducible.

If it is, try running the same queries as this function does manually. You may find them by running \sf+ on the function in psql, or, if you are running Bucardo5, by looking at the schema definition: https://github.com/bucardo/bucardo/blob/master/bucardo.schema


Will alone bin log ignore DB mysql helps in slave read only mode Please correct me if I'm wrong .
In Master - Slave setup:

Case 1:

  If I didn't  enable Binlog ignore DB mysql: In future ill lose all
  users created in master node incase of master crash or slave to master
  and master to slave scenario.

Case 2:

  If I did enable binlog ignore DB mysql and an user has  all
  privileges, he can directly connect to slave DB[if he is fishy], and
  manipulate data in slave which causes inconsistency.

How to over come this scenario? I'm new to this. Any help is highly appreciated.

After series of testing.. this scenario only happens if we grant all privileges on every database (.)[ I think it will assume this specific user as root privileged and allows to access any database. In any other scenario it will reject with below error.

*ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement*


How to pass a table type with an array field to a function in postgresql i have a table called book

CREATE TABLE book
(
  id smallint NOT NULL DEFAULT 0,
  bname text,
  btype text,
  bprices numeric(11,2)[],
  CONSTRAINT key PRIMARY KEY (id )
)

and a function save_book

CREATE OR REPLACE FUNCTION save_book(thebook book)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN

    update book set
    bname=thebook.bname,
    btype=thebook.btype,bprices=thebook.bprices  WHERE id=thebook.id;

    IF FOUND THEN
        myoutput:= 'Record with PK[' || thebook.id || '] successfully updated';
        RETURN myoutput;
    END IF;

    BEGIN
        INSERT INTO book values(thebook.id,thebook.bname,thebook.btype,
        thebook.bprices);
        myoutput:= 'Record successfully added';
    END;
 RETURN myoutput;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

now when i call the function

SELECT save_book('(179,the art of war,fiction,{190,220})'::book);

i get the error

ERROR: malformed array literal: "{190"
SQL state: 22P02
Character: 18

i dont understand because i dont see any error in the format of the array, any help?

This sort of thing gets complicated.  I am working on some related projects right now.  The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so:

SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book);

should work.  In essence a neat trick is creating a csv and enclosing in tuple or array identifiers.  The big issue is that you have to deal with escaping (doubling quotes at every level as needed).  So the following is exactly equivalent:

SELECT save_book('(179,"the art of war","fiction","{""190"",""220""}")'::book);

The second approach is to use a row constructor:

SELECT save_book(row(179,'the art of war','fiction', array[190,220])::book);

The first solution has the obvious advantage of being able to take advantage of existing programming frameworks for CSV generation and escaping.  The second is cleanest in SQL.  They can be mixed and matched.


REPLACE(orig,match,replacement) multiple matches in one query I'm trying to categorize Magic The Gathering cards. Here is a simplified version of what I'm doing/trying.

Is it reasonable/doable to get my desired output from mysql?  If so, how?

Or am I going to need to use some php? (which I can figure out pretty easily) I'd rather do it through a query if i can.

I run:

SELECT REPLACE(orig.text, matches.text, matches.replacement)
    FROM orig
JOIN matches
    ON orig.text LIKE CONCAT('%',matches.text,'%') ;

I want:

| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]

In the actual app, there would be another column, like the card name.

I actually get:

| REPLACE(orig.text,matches.text,matches.replacement) |
+-----------------------------------------------------+
| [who] run really fast                               |
| [who] run really slow                               |
| [who] shop really fast                              |
| [who] shop really qu[who]ckly                       |
| [who] shop really slowly                            |
| i [action] really fast                              |
| i [action] really slow                              |
| i run really [speed]                                |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i [action] really fast                              |
| i [action] really quickly                           |
| i [action] really slowly                            |

orig table

| i run really fast     |
| i run really slow     |
| i shop really fast    |
| i shop really quickly |
| i shop really slowly

matches table

| text    | replacement |
+---------+-------------+
| i       | [who]       |
| run     | [action]    |
| fast    | [speed]     |
| slowly  | [speed]     |
| quickly | [speed]     |
| shop    | [action]    |

A stored function did the trick. I spent a lot of time playing with RECURSIVE CTE only to find that stored functions have loops. Twas fun.

Anyway. I also have a table 'replaced' with columns 'text' (the original) and 'tokenized' (the updated).

So I run:
UPDATE replaced SET tokenized = tokenize_text(text);

and i have stored function:

DELIMITER $$
DROP FUNCTION IF EXISTS tokenize_text $$
CREATE FUNCTION tokenize_text(the_text varchar(1000)) RETURNS VARCHAR(1000)
    READS SQL DATA
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_match varchar(1000) DEFAULT "";
 DECLARE v_replacement varchar(1000) DEFAULT "";
 DECLARE v_output varchar(1000) DEFAULT CONCAT(' ',the_text, ' ');

 -- declare cursor for matches
 DEClARE matches_cursor CURSOR FOR
    SELECT text, replacement FROM matches;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = TRUE;

 OPEN matches_cursor;

 change_text: LOOP

 FETCH matches_cursor INTO v_match, v_replacement;

 IF v_finished THEN
 LEAVE change_text;
 END IF;

 -- build email list
 SET v_output = REPLACE(v_output, CONCAT(' ',v_match,' '), CONCAT(' ',v_replacement,' '));

 END LOOP change_text;

 CLOSE matches_cursor;
 RETURN v_output;
END$$

DELIMITER ;

To test, I do select * FROM replaced'; and get:

+-----------------------+---------------------------------+
| text                  | tokenized                       |
+-----------------------+---------------------------------+
| i run really fast     |  [who] [action] really [speed]  |
| i run really slow     |  [who] [action] really slow     |
| i shop really fast    |  [who] [action] really [speed]  |
| i shop really quickly |  [who] [action] really [speed]  |
| i shop really slowly  |  [who] [action] really [speed]  |
+-----------------------+---------------------------------+

Which is correct. It turns out I didn't have a match for 'slow', so it doesn't get replaced with [speed] like the other speed-specific adjectives.


Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7 I am using Oracle 11g and want to export a table using below statement

C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER

After this statement cmd is giving this

UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

I proceeded with

Username:  SYS@SMSPSETUP AS SYSDBA
Password:

And finally I got

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I have created directory 'Mydir' as below

SQL>CREATE DIRECTORY Mydir AS 'C:\Users\osman\Desktop\Export';

And gave everyone full privileges to this folder from the windows folder system.

When run the below statement:

select directory_name, directory_path from dba_directories; where directory_name='Mydir';

I can see the path 'C:\Users\osman\Desktop\Export'

So why am I getting this error?

I just wanted to chime in and share what got me past this error.  In my case (Ora 11.2.0.3 on Win 7) I found I needed the trailing '\' in my directory path.

Replaced:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID'

With:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID\'


how to interact to second database through API on mongodb I am on Windows environment. I am using Mongoose and nodejs for my project. I have MongoDB Replication i.e 27017(primary), 27018(slave)and 27020 is (Arbitar). now I have One more seperate db named as secondary which is running on port 27021.

My mongodb Version is

MongoDB shell version v3.4.9

For Interaction with backend I made the API. Now my problem is I want to fetch some information from my second server only when specific API calls from Frontend. This API is made/written on Primary server i.e Under Replication server.

I create the logic by which I get the information but just after my primary server is closed and not open, and generate an error that is

  MongoError: topology was destroyed and then-No connection available.

My approach was.

    router.post('/test, function(req, res) {
      mongoose.connection.close( function () {
        console.log('Primary Server close Properly');
      });
      const options = {
        useMongoClient: true,
        autoIndex: false, // Don't build indexes
        reconnectTries: 10000, // Never stop trying to reconnect
        reconnectInterval: 500, // Reconnect every 500ms
        poolSize: 10, // Maintain up to 10 socket connections
      // If not connected, return errors immediately rather than waiting for reconnect
        bufferMaxEntries: 0
      };
      mongoose.createConnection('mongodb://127.0.0.1:27021/secondary', options);
            Message.find({}).limit(2).exec(function(error, data) {
                if(error) {
                    console.log(error);
                } else {
                    res.send(data);
                    mongoose.connection.close( function () {
                        console.log('Secondary Server close Properly');
                    });
                    const options = {
                      useMongoClient: true,
                      autoIndex: false, // Don't build indexes
                      reconnectTries: 10000, // Never stop trying to reconnect
                      reconnectInterval: 500, // Reconnect every 500ms
                      poolSize: 10, // Maintain up to 10 socket connections
                      // If not connected, return errors immediately rather than waiting for reconnect
                      bufferMaxEntries: 0
                    };
                    mongoose.connect('mongodb://127.0.0.1:27017, 127.0.0.1:27018, 127.0.0.1:27020/sfr_new?replicaSet=sfr', options);
                    var db = mongoose.connection;
                    console.log('Original Connection Open Successfully');
                    db.on('error', console.error.bind(console, 'MongoDB connection error:'));
                        }
                    });

})

By this I am getting the response from second server, but my first server not start. Any help is really Appreciated.

As per MongoDB Blog here and MongoDB Jira Blog here It seems to mean your node server's connection to your MongoDB instance was interrupted while it was trying to write to it.

As here  Mongoose and the MongoDB driver both do connection buffering, so you need to shut both buffering mechanisms off to make your database operations fail fast when mongoose is not connected. By default, mongoose will wait until you reconnect before actually executing an operation. For example, the below findOne() callback will execute after the 'reconnect' event is emitted.

// When the mongodb server goes down, mongoose emits a 'disconnected' event
mongoose.connection.on('disconnected', () => { console.log('-> lost connection'); });
// The driver tries to automatically reconnect by default, so when the
// server starts the driver will reconnect and emit a 'reconnect' event.
mongoose.connection.on('reconnect', () => { console.log('-> reconnected'); });

// Mongoose will also emit a 'connected' event along with 'reconnect'. These
// events are interchangeable.
mongoose.connection.on('connected', () => { console.log('-> connected'); });

const MyModel = mongoose.model('Test', new mongoose.Schema({}));

// Stopping the server will emit a 'disconnected' event
await server.stop();
console.log('stopped server');

// Using callback by design, because when the driver is disconnected
// it "buffers" operations. If the 'disconnected' event was emitted, the driver
// will hold all operations until it reconnects, so "Finished query"
// won't print until **after** "restarted server" **and** "connected"
MyModel.findOne({}, function(error) {
  console.log('Finished query', error);
});

await new Promise(resolve => setTimeout(() => resolve(), 2000));

// Restarting the server will emit the 'reconnect' and 'connected' events
await server.start();
console.log('restarted server');

For your further ref here and here


SELECT optimization in Postgres 10 I want to select last unique rows based on time, so:

 SELECT DISTINCT ON (track) *
 FROM   eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco.tracks ORDER BY id DESC LIMIT 1)  - INTERVAL '300 seconds')  ORDER BY id DESC LIMIT 1)
 ORDER  BY track, time_track DESC;

It takes 20 seconds, that's too slow.
If I replace id by the actual value, the query takes only 2 ms:

SELECT DISTINCT ON (track) *
FROM   eco.tracks WHERE id > 48000000
ORDER  BY track, time_track DESC;

That subquery alone takes only 2 ms.

SELECT id FROM eco.tracks
WHERE  time_track < ((SELECT time_track FROM eco.tracks ORDER BY id DESC LIMIT 1) - INTERVAL '300 seconds')
ORDER  BY id DESC
LIMIT  1

Why the big difference?

My crystal ball says the two queries end up using different query plans. You can verify by running EXPLAIN on each. More useful details with EXPLAIN (ANALYZE, BUFFERS).

The subquery gets the greatest id with time_track more than 5 minutes before the one of the greatest id overall in table eco.tracks. Assuming a strong positive correlation between id and time_track, humans get a rough idea of the work to be done here: it only concerns the (probably very few) latest rows within the last 5 minutes in the table. But Postgres has no idea at the time of planning the query.

(Aside: It may be a lingering logic bug to assume that serial IDs are in sync with timestamps in the column time_track. Not enough information to say more.)

For the first (slow) query, Postgres has to prepare a plan that best copes with any value that might come out of the subquery. That may very well be a sequential scan, which turns out to be a bad choice for only the few latest rows.

For the second (fast) query, Postgres knows from the given value what to expect. It can start by identifying the few relevant rows with an index or bitmap index scan on an index on (id) which undoubtably exists (again assuming that's the PK). The rest is peanuts.

Possible solutions

There are many ways. Anything to get Postgres to pick the appropriate query plan ...

1. Two separate queries

Dead simple. Build the second query with the actual value returned from the first. The downside: two round trips to the db server collecting two times network latency and varying overhead.

2. Dynamic SQL

CREATE OR REPLACE FUNCTION f_latest_tracks(_intv interval = '300 seconds')
  RETURNS SETOF eco.tracks AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT DISTINCT ON (track) *
 FROM   eco.tracks
 WHERE  id > $1
 ORDER  BY track, time_track DESC'
USING (
   SELECT id
   FROM   eco.tracks
   WHERE  time_track < (SELECT time_track - _intv FROM eco.tracks ORDER BY id DESC LIMIT 1)
   ORDER  BY id DESC
   LIMIT  1
   );

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_latest_tracks();
SELECT * FROM f_latest_tracks('10 minutes');

Why? The manual about Executing Dynamic Commands:

  The important difference is that EXECUTE will re-plan the command on
  each execution, generating a plan that is specific to the current
  parameter values; whereas PL/pgSQL may otherwise create a generic plan
  and cache it for re-use. In situations where the best plan depends
  strongly on the parameter values, it can be helpful to use EXECUTE to
  positively ensure that a generic plan is not selected.

Downside: You need to create a server-side function introducing a dependency on  table eco.tracks. And you need to be comfortable with PL/pgSQL, of course.

Using a parameter default for conveniencse. See:

Add function parameter to SQL query WHERE clause


Delete index while query is running, is it possible? I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still running, and would this speed things up?

Actually, if this changes anything, it is a uniqueness constraint over three columns that creates the index.

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course.

No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index.

No, I don't think your query should take more than three days, even if you import every link that every Wikipedia site on earth ever had to offer. There is probably something less than ideal in your command or setup or hardware or how you read / write the data.
For starters, an operation of that magnitude would rather use COPY:

Increased performance from single statement, multiple row inserts?


How to add IF ELSE in mysql query My query is like this

select * from mytable where rank <= 10000 and priority = 1

I want to rewrite it like this.

Select records which are under a specific value and priority is one.
If no records found with priority = 1, then run the query without priority.

select * from mytable where rank <= 10000

I know it can be done with php but can it can done in one query instead using php ?

thanks

There may be a more elegant or performant solution, but here's a simple option:

select * from mytable
 where rank <= 10000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE rank <= 10000
                          AND priority = 1
                      )
       )
;

This works as follows:  the NOT EXISTS statement will either evaluate to TRUE for every record where the rank meets our criteria, or to FALSE for every such record. So, if none of the records that meet our other criteria have priority = 1, this is TRUE for all those records, and all records that meet our other criteria will be returned.

If there are records that meet our other criteria and have priority = 1, then this statement will be FALSE for all records that meet our other criteria. In that case, only records where the other branch of the OR is TRUE - where priority = 1 - will be returned.

NOTE: for this to work properly, the NOT EXISTS subquery must check all the query's criteria. If you actually want records where:

fieldA is between Jan 1 2017 and March 31 2017; and
fieldB is less than 240000

Then the query would have to be:

select * from mytable
 where fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
   and fieldB < 240000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
                          AND fieldB < 240000
                          AND priority = 1
                      )
       )
;


Joining data from legacy database I'm trying to figure out how to write this query.

Table 1 - Booking

BOOKING_ID | BOOKING_DATE | etc...
1          | 2013/01/01   |
2          | 2013/01/01   |

Table 2 - BOOKING_PROCEDURE_TYPE

BOOKING_PROCEDURE_TYPE_ID | BOOKING_ID | LAST_UPDATE_TIME | COMMENTS
1                         | 1          | 10:00            | blah blah
2                         | 1          | 08:45            |
3                         | 1          | 06:00            | referred by

What I want is to join table two where the booking ID matches, but only include the most recent update for each row.

I've written the query 20 different ways, but I keep ending up with a row for every instance of BOOKING_ID.

This is the closest I've gotten so far

SELECT bpt.*
FROM pas.BOOKING_PROCEDURE_TYPE bpt
INNER JOIN (SELECT
                b.BOOKING_ID,
                MAX(b.LAST_UPDATE_TIME) as LUP
FROM pas.BOOKING_PROCEDURE_TYPE b

GROUP BY b.BOOKING_ID)x on bpt.BOOKING_ID = x.BOOKING_ID AND bpt.LAST_UPDATE_TIME = x.LUP

I know I'm probably making some really obvious stuff up and would appreciate some help from better people than I

You can use ROW_NUMBER (Transact-SQL) to enumerate the rows in BOOKING_PROCCEDURE_TYPE partitioned by BOOKING_ID and ordered by LAST_UPDATE_TIME descending.

select *
from BOOKING as B
  inner join (
              select *,
                     row_number() over(partition by BOOKING_ID
                                       order by LAST_UPDATE_TIME desc) as rn
              from BOOKING_PROCCEDURE_TYPE
             ) as BPT
    on B.BOOKING_ID = BPT.BOOKING_ID
where BPT.rn = 1


How can I make queries secret? I have a project whose activity I need to keep private, and I'm not the server administrator.
If my app updates a value for the user with id 1, it would probably say something like UPDATE 'users' SET 'Name' = 'George' WHERE 'Id' = 1; and that will show in the Apache log.
What can I do to avoid this?

The Apache logs show what the Apache Web Server has been asked to do.
It generally knows (nor cares) not a jot about SQL.  Of any flavour.
Please, please, please tell me you're not submitting raw SQL statements to your web application through the URL and/or QueryString and executing them!
Obligatory XKCD Reference: Little Bobby Tables.
This is a huge Security Risk and, if Apache really is logging queries, including Personal Data into its own, largely unsecured, log file then you're heading into very dangerous waters with regards to Data Protection legislation (e.g. GDPR).


Filter records and find those with minimal date I have the following tables in Microsoft SQL Server:

Users: Includes uID (primary key), birthDate, name
Conversations: Includes convID (primary key), hostID (foreign key for uID in Users)
Participants: Includes uID (foreign key for uID in Users), convID (foreign key for convID in Conversations).

I need to write a query which finds the name, ID and birth date of the oldest user who didn't participate in any conversation, and that his name contains the letter 'b'. If there is more than one user, I need to return all of them.
I don't know how to both filter the users, and than find those with the minimal birth date (the oldest).

This query should obtain what you need (assuming "didn't participate in any conversation" means no records exist for that user in the Participants table.)
WITH CTE_Users_SortedByBirthDate AS -- Sort Users by their birthdate
(
    SELECT [uID], birthDate, [name], ROW_NUMBER() OVER (ORDER BY birthDate) AS OldestUser -- Note if two users have the same birthDate they'll both be returned, if you add uID after birthDate in the ORDER BY clause, then it'll guarantee you only one user: the oldest user by birthDate and by user ID
    FROM Users
    WHERE [name] LIKE '%b%' -- User's name contains the letter "b"
)

SELECT U.[uID], U.birthDate, U.[name]
FROM CTE_Users_SortedByBirthDate AS U
LEFT JOIN Participants AS P
    ON U.[uID] = P.[uID]
WHERE P.[uID] IS NULL -- Only users who have no Participant records
    AND U.OldestUser = 1 -- Returns only the oldest user(s) by birthDate


Cpu-Alert in SQL Server -2012 standard edition My question is an extension to this.

We have SQL Server Agent alerts(Deadlocks, Network IO etc) to monitor SQL Server performance. So i would like to write an alert for CPU increase percentage. Since We are using SQL Server-2012 standard edition we are not able to write an alert for it. So i have googled and found options like; we can raise an
alert through Perfmon(Performance monitor). Please look below image.

In alert task tab, "Run this task when alert is triggered" in this box i have given the path of .BAT file (which will run a query and send the current cpu expensive statements in a mail).

But alert is not firing when cpu percentage crosses certain limit. I guess the way i am configuring in perfmon is wrong for cpu alert.
Can any one please suggest blogs or links for raising cpu alert from performance monitor? or Please suggest any other way to do this.

Basically, I am trying to call a batch file from performance monitor by using alerts. Please advice.

Have you tried the DMV sys.dm_os_performance_counters ? Using this view you can get several perf. counters, where you can sample by intervals to have an idea how your system is going.

This is the documentation by Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql

Here you can find a few examples how to use it:
http://www.sqlservercentral.com/articles/Performance+Tuning/71784/

And here is an example how to get your current CPU usage:

select perfCount.object_name
     , perfCount.counter_name
     , CASE
            WHEN perfBase.cntr_value = 0
            THEN 0
            ELSE (CAST(perfCount.cntr_value AS FLOAT) / perfBase.cntr_value) * 100
      END AS cntr_Value
FROM
    (select *
        from sys.dm_os_performance_counters
        where object_Name = 'SQLServer:Resource Pool Stats'
          and counter_name = 'CPU usage %'
     ) perfCount
inner join (select *
            from sys.dm_os_performance_counters
            where object_Name = 'SQLServer:Resource Pool Stats'
            and counter_name = 'CPU usage % base'
            ) perfBase
ON perfCount.Object_name = perfBase.object_name


Combining multiple queries I have 2 queries given below. Now I want to have one quey by combining the two queries so as to get the table with columns (Tag Number, Primary Name and Discoverd Name)

Query 1:

select  RT.[TagName] as 'Tag Number',
        RA.SourceName as 'Primary Document',
        RA.SourceRevision
from dbo.REP_TTA as RT
join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
where RA.[AssocName]='is a document for'

Query 2:

select RT.[TagName] as 'Tag Number',
       RA.SourceName as 'Discoverd Document',
      RA.SourceRevision
from dbo.REP_TransposedTagAttributes as RT
join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
where RA.[AssociationName]='refers to'

As far as I can guess, you have 'Primary Document' and 'Discovered Document' in different rows of REP_ASS, which means you'll need to join REP_ASS twice anyway... So here it goes:

select RPrimary.[TagName] as 'Tag Number',
       RPrimary.sourceName as 'Primary Document',
       RDiscovered.SourceName as 'Discoverd Document',
       RPrimary.SourceRevision
from
    (select  RT.[TagName],
            RA.SourceName,
            RA.SourceRevision
    from dbo.REP_TTA as RT
    join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
    where RA.[AssocName]='is a document for'
    ) AS RPrimary
inner join --or outer if you don't have them all in both tables yet want them all
    (select RT.[TagName],
           RA.SourceName
    from dbo.REP_TransposedTagAttributes as RT
    join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
    where RA.[AssociationName]='refers to'
    ) AS RDiscovered
on RPrimary.[TagName] = RDiscovered.[TagName]


How to verify the password generated by ENCRYPT? I use the ENCRYPT function to generate password for my email users.

SELECT ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))

This function will generate a long encrypted string which looks something like this:

$6$103b1e8ad6b6db7d$CyTHP3ooA8N8uWtKmQOZFjRkTss60o3ckb6CPYG01uJqiLjGMFU0y9jtCA13MFtJ3LiPrKv5JVcfRaYg8B7YL0

Now my question is how I can verify the password? What I want to do is I store the encrypted password in the database. And I want my user to be able to modify their password as long as they provide the correct old password. So when the users provide their old passwords, I need to verify whether the old password they provided is correct or not.

When you store a password you have to store encrypt(password,salt) and salt for this user. To check if input from user is the right password you comapre encrypt(input,salt) with the stored encrypted string. Chanck the section about 8.6.2 What Is Salt? in
Practical UNIX & Internet Security, by Simson Garfinkel & Gene Spafford from O'Reilly.


SQL Server 2000 Database Monitoring I have never worked on SQL Server 2000 and I got a requirement to check capture the following information to one particular database.

DBName  CurrentDate SPID    BatchDuration   ApplicationName HostName    LoginName   SQLQuery

I tried the query below but it in first place did not return any information or does not meet the requirement as it does not capture the ApplicationName and SQL Text.

SELECT
    DB_NAME(dbid) as dbName
,   CURRENT_TIMESTAMP as CurrentDate
,   P.spid
,   right(convert(varchar,
        dateadd(ms, datediff(ms, P.last_batch, getdate()), '2000-01-01'),
        121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
,   P.cmd
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                ,'MIRROR HANDLER'
                ,'LAZY WRITER'
                ,'CHECKPOINT SLEEP'
                ,'RA MANAGER')
and DB_NAME(dbid) ='master'
order by batch_duration desc

Also when I try to setup this script via SQL Agent it only allows to run it every one minutes interval. Is there any other better way to run it like every 5 seconds? or maybe I should be using another method instead e.g. capturing the logins with required information via trigger instead?

Any input will be appreciated.

I found a solution online: http://strictlysql.blogspot.com/2010/07/lock-monitoring-sql-server-2000.html. Modified the script to fulfill my requirement.

Created a table in our monitoring database:

CREATE TABLE MyDBMonitoring
  (
     DatabaseName        varchar(35),
     Time                   datetime,
     SPID                        INT,
     Query              varchar(1000),
     QueryStatus         VARCHAR(35),
     LoginName           VARCHAR(50),
     HostName            VARCHAR(50),
     LastBatch              datetime,
     Command              VARCHAR(50),
     ProgramName          VARCHAR(100),
     OpenTransactions           INT
  )
Modified the script from the link and only selected the fields I need and insert the result into the monitoring table by setting up a SQL Agent Job on the Server. Will provide the result to client in two days.

CREATE TABLE #sp_lock_details
  (
     spid     INT,
     dbid     INT,
     objid    INT,
     indid    INT,
     TYPE     VARCHAR(100),
     resource VARCHAR(1000),
     mode     VARCHAR(10),
     status   VARCHAR(50)
  )

INSERT INTO #sp_lock_details
EXEC Sp_lock

DECLARE @id INT
DECLARE @max INT
DECLARE @spid INT
DECLARE @exec_qry VARCHAR(500)
DECLARE @monitored_dt DATETIME

SET @monitored_dt = Getdate()

CREATE TABLE [#sysproc]
  (
     id             INT IDENTITY(1, 1),
     [spid]         [SMALLINT] NOT NULL,
     [dbname]       NVARCHAR COLLATE sql_latin1_general_cp1_cs_as NULL,
     [cpu]          [INT] NOT NULL,
     [memusage]     [INT] NOT NULL,
     [status]       NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [loginame]     NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [hostname]     NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [lastwaittype] NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [last_batch]   [DATETIME] NOT NULL,
     [cmd]          NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [program_name] NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     QUERY          VARCHAR(500),
     open_tran      INT
  )
ON [PRIMARY]

CREATE TABLE #dbccinfo
  (
     eventype  VARCHAR(255),
     param     INT,
     eventinfo VARCHAR(255)
  )

INSERT INTO #sysproc
            (spid,
             dbname,
             cpu,
             memusage,
             status,
             loginame,
             hostname,
             lastwaittype,
             last_batch,
             cmd,
             program_name,
             open_tran)
SELECT spid,
       Db_name(MASTER.dbo.sysprocesses.dbid) AS dbname,
       cpu,
       memusage,
       status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran
FROM   MASTER.dbo.sysprocesses
WHERE  spid > 50

SET @max = @@IDENTITY
SET @id = 1

WHILE ( @id <= @max )
  BEGIN
      SELECT @spid = spid
      FROM   #sysproc
      WHERE  id = @id

  SET @exec_qry = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(3), @spid) + ')'

  INSERT INTO #dbccinfo
  EXEC (@exec_qry)

  UPDATE sys
  SET    QUERY = eventinfo
  FROM   #sysproc sys,
         #dbccinfo
  WHERE  @id = id

  TRUNCATE TABLE #dbccinfo

  SET @id = @id + 1

END

INSERT INTO dba.dbo.MyDBAMonitoring
  (
     DatabaseName,
     Time,
     SPID,
     Query,
     QueryStatus,
     LoginName,
     HostName,
     LastBatch,
     Command,
     ProgramName,
     OpenTransactions
  )
  SELECT dbname AS DatabaseName,
       CURRENT_TIMESTAMP AS 'TIME',
       #sysproc.spid AS SPID,
       QUERY,
       #sysproc.status                AS query_status,
       loginame,
       hostname,
       last_batch,
       cmd,
       program_name,
       open_tran
  FROM   #sysproc,
           #sp_lock_details
    WHERE  #sp_lock_details.spid = #sysproc.spid
    and dbname = 'ODS;

DROP TABLE #dbccinfo
DROP TABLE #sysproc
DROP TABLE #sp_lock_details


sysssislog table in a user database How can the sysssislog table to show up in a user database's system tables?  This is for SQL server 2012.

By default, sysssislog table only exists under System Table for the System databases [msdb].
If you are seeing sysssislog in a user database, then you must have a package that is setup for execution logging and it creates the table in the specified database if it does not exist.


Syncing Server Objects across AG nodes This is my first question, hope I do it right!

I am working on creating a process that will sync server objects between our AG nodes. We are using SQL Server 2016 with 4 nodes. Two nodes in one data center and two nodes in a different data center.  I am currently using PowerShell to copy my logins, jobs, SSIS catalog, operators, and linked servers. I want to automate deploying changes made to these objects across all four nodes. Is there a tool made for this already?

If you're already comfortable with Powershell, check out dbatools.io, which I think provides many of the functionalities you're looking for.

Some time ago, I wrote a hack that synchronizes logins using linked servers, which you may find helpful.

Regardless of what tool you choose, I would create an Agent job that runs on all nodes and checks if it's running on a primary or secondary. If it's on the secondary, it can pull the latest version from the primary.


Postgres 10.3 could not bind IPv6 address, database system was not properly shut down I am running Postgres v10.3 through Docker (docker-compose, specifically).  It is not turning back on.  I get the following error message when I try to turn it on:

postgres           | pg_ctl: another server might be running; trying to start server anyway
postgres           | waiting for server to start....2018-05-15 12:37:07.552 UTC [22] LOG:  listening on IPv4 address "127.0.0.1", port 5432
postgres           | 2018-05-15 12:37:07.552 UTC [22] LOG:  could not bind IPv6 address "::1": Address not available
postgres           | 2018-05-15 12:37:07.552 UTC [22] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
postgres           | 2018-05-15 12:37:07.558 UTC [22] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres           | 2018-05-15 12:37:07.804 UTC [23] LOG:  database system was interrupted while in recovery at 2018-05-15 12:35:58 UTC
postgres           | 2018-05-15 12:37:07.804 UTC [23] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
postgres           | .........2018-05-15 12:37:17.735 UTC [23] LOG:  database system was not properly shut down; automatic recovery in progress
postgres           | .2018-05-15 12:37:17.935 UTC [23] LOG:  redo starts at 1/5E964BA8
postgres           | pg_ctl: server did not start in time
postgres           | ................................................. stopped waiting

I may have shut down the database as I was writing to it, which is the only thing I can think of that would have caused this.  I do not have a backup which is why I'd like to restore what I've already done (except for the table that I was writing to when I shut down).

Things I've tried:

Restarting the docker container and clearing out the cache (docker system prune -f)
Remove the postmaster.pid file and restarting

This gives me a could not open file "postmaster.pid": No such file or directory error and generates a postmaster.pid file which then allows me to get back to my original error

Those messages are unrelated.  The first one will show up every time you start postgresql if your machine is not configured to use IPv6.  You can just ignore it if you don't care about IPv6.

The other one is a bit more complex.  'system was not properly shut down' means it was interrupted without being given a chance to do a checkpoint and shut down. 'database system was interrupted while in recovery' means that it had been interrupted before, and was then interrupted again before it was able to clean up from the previous interruption, and is now trying to clean up from both of those previous interruptions.

The 'stopped waiting' means the pg_ctl call in your startup script is not patient enough to for the recovery to happen (which seems to me to be a poor decision to make in a startup script).  But then the issue is, what then?  Sometimes when pg_ctl runs out of patience, it just exits nicely and lets the recovery proceed in the background.  Sometimes it clubs the recovery process over the head.  Which of these it does seems to be pretty unpredictable (to me, anyway) it depends on your OS and OS version, and how your start-up script is written, and maybe what logging options you have configured.

Based on the log file show posted, it is likely that on your system pg_ctl clubs the recovery process over the head once it runs out of patience.  So you shutdown the system uncleanly when it was in the middle of something hectic.  Restarting it will take a lot of time, because it has to clean up whatever it was in the middle of.  pg_ctl ran out of patience and killed it (this part was not in your posted logfile snippet).  Then pg_ctl ran out of patience again and killed (this part is in your snipped).

Your start-up script should probably pass a large timeout value to the -t flag of pg_ctl, to make it patient.


Password column from sys.user$ is null in 18c, why? Why the password column from sys.user$ is null? My version is 18c. I've made a test.
SQL> alter user harry identified by 12345678;

User altered.

SQL> select name,password from sys.user$ where name = 'HARRY';

NAME                           PASSWORD
------------------------------ ----------------------------------------
HARRY

SQL>

I'd like to use the password from harry in another user with
alter user some_user identified by values 'hash_here'

Check the SPARE4 column.
Quoting from https://www.experts-exchange.com/articles/855/How-Oracle-Stores-Passwords.html

11g introduces new security and the ability to support case sensitive
passwords.
First, DBA_USERS no longer exposes the PASSWORD value, instead it will
be NULL and you will have to go to SYS.USER$ to find the hash value.
However, if your PASSWORD_VERSIONS does not contain 10g then
SYS.USER$.PASSWORD will not be reliable.  The reason is the hashing
algorithm populating that field doesn't support case sensitivity.  So,
regardless of the sensitivity setting and regardless of the upper or
lower case of your password the hash will be the same.  If your
PASSWORD_VERSIONS is 11g only then you will need to look in the
SYS.USER$.SPARE4 column and you will see a much larger hex number.
This is because Oracle has switched to the SHA-1
(http://en.wikipedia.org/wiki/SHA-1) algorithm.


Constraint exclusion to fetch latest N rows I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted rows, so at the beginning I just queried for:

select id, logdate, content from measurements order by logdate DESC limit 500;

A few days later, I found out it was faster to set id to be (for this example) 10000000000 - extract(epoch from logdate), and use it as PRIMARY KEY, so

select id, date, content from measurements limit 500;

would naturally order by id, therefore yielding the latest records.

As the table grew, it became unmanageable, so I resorted to partitioning. I did it just as the documentation says:

CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

and I distributed existing rows onto their respective partition.

The problem is, I'm partitioning on logdate, but when querying I don't know the timespan. When I query for the last 500 rows, I can't know if they are from last week, last month of last quarter. Therefore, the query planner always scans all the partitions.

I can't believe no one has dealt with this same problem before, it sounds trivial and yet it has me puzzled.

Misconception 1: "Natural order"

<query without ORDER BY>
... would naturally order by id, therefore yielding the latest records.

There is no natural order in a SELECT statement. Without ORDER BY you get rows in arbitrary order. Generally that will be the cheapest order in which Postgres can satisfy your query, i.e. the order in which tuples are stored physically or in which they are retrieved after an index look-up. But there is no guarantee whatsoever. If your statement seemed to work, this was pure luck / coincidence and it can break at any time.
Use your first query instead. If logdate is, in fact, of type date, or if you need to be sure, you should add more ORDER BY items to break ties and get a stable sort order. If you don't care which, append your (new) primary key (see below):
SELECT id, logdate, content
FROM   measurement
ORDER  BY logdate DESC, measurement_id DESC
LIMIT 500;

If the latest row (biggest measurement-id) is guaranteed to have the latest logdate, you can just ORDER BY measurement_id DESC, but don't take this for granted. In a multi-user environment a row with a later logdate can be written sooner than another row with a sooner logdate.
This is one reason why your idea for the new primary key is not very useful:

10000000000 - extract(epoch from logdate), and use it as PRIMARY KEY

The other reason: it is bound to fail sooner or later if logdate is not guaranteed to be unique - which it most probably isn't.
Use a serial column measurement_id as primary key instead. Or bigserial if you expect more than 2147483647 rows over time.
Index
You claim you did it just as the documentation says, and the documentation says:

For each partition, create an index on the key column(s), as well as ...

And further down:

We probably need indexes on the key columns too:
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...

The only tiny difference: the example in the manual uses the more sensible singular form for the table name: measurement instead of measurements.
If you go with my advice:
ORDER  BY logdate DESC, measurement_id DESC

make that:
CREATE INDEX measurement_y2006m02_logdate
ON measurement_y2006m02 (logdate DESC, measurement_id DESC);
...

More on why that would probably help:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

Misconception 2: "scan all partitions"

Therefore, the query planner always scans all the partitions.

The query planner will plan to check all partitions in sequence. But as soon as the query is satisfied (500 rows are retrieved), it will stop executing. Test with EXPLAIN ANALYZE, you will see the annotation (never executed) behind remaining partitions.
If the planner shouldn't be smart enough to derive the best sequence in which to scan from your setup (can't test right now), you can give a hand with a UNION ALL query on the partitions:
(
SELECT measurement_id, logdate, content FROM measurement_y2006m03_logdate
ORDER  BY logdate DESC, measurement_id DESC
)            -- parens needed to include ORDER BY in individual legs of UNION query
UNION ALL
(
SELECT measurement_id, logdate, content FROM measurement_y2006m02_logdate
ORDER  BY logdate DESC, measurement_id DESC
)
...          -- latest partition first

LIMIT 500;

But that may not be necessary.


MongoDB - Database vs Collection memory-wise Lets say for example i need to have 5 collections, each collection is about 10GB.
What is the difference in performance, with emphasis on memory usage, between assigning each said collection to a database, versus having all of these collections in the same database?

Also, in this scenario, whats the difference between MMAPv1 storage engine and WiredTiger?

EDIT: Spoken to a MongoDB team and they assured me there should be no difference between having multiple collections in a single database versus one collection for each multiple databases.

MongoDB - Database vs Collection memory-wise

As you said you have MongoDB version 2.4, the MongoDB documentation here and here specifications

WiredTiger Storage Engine

Starting in MongoDB 3.2, the WiredTiger storage engine is the
default storage engine.
Minimum log record size for WiredTiger is 128 bytes. If a log record
is 128 bytes or smaller, WiredTiger does not compress that record.

Memory Use

With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

By default, WiredTiger uses Snappy block compression for all collections and prefix compression for all indexes. Compression defaults are configurable at a global level and can also be set on a per-collection and per-index basis during collection and index creation.

  Note : For existing deployments, if you do not specify the --storageEngine or the storage.engine setting, the version 3.2+ mongod instance can automatically determine the storage engine used to create
  the data files in the --dbpath or storage.dbPath.

MMAPv1 Storage Engine

MMAPv1 is the default storage engine for MongoDB versions 3.0 and
earlier.
MongoDB 4.0 deprecates the MMAPv1 Storage Engine and will remove
MMAPv1 in a future release. To change your MMAPv1 storage engine
deployment to WiredTiger Storage Engine, see:

a.) Change Standalone to WiredTiger

b.) Change Replica Set to WiredTiger

c.) Change Sharded Cluster to WiredTiger

Memory Use

With MMAPv1, MongoDB automatically uses all free memory on the machine as its cache. System resource monitors show that MongoDB uses a lot of memory, but its usage is dynamic. If another process suddenly needs half the server’s RAM, MongoDB will yield cached memory to the other process.

Technically, the operating system’s virtual memory subsystem manages MongoDB’s memory. This means that MongoDB will use as much free memory as it can, swapping to disk as needed. Deployments with enough memory to fit the application’s working data set in RAM will achieve the best performance.

  For example i need to have 5 collections, each collection is about
  10GB. What is the difference in performance, with emphasis on memory
  usage, between assigning each said collection to a database, versus
  having all of these collections in the same database?

As per google group forum by Mr.Kevin Adistambha defined here it is not advisable to push any technology to such extreme numbers without careful thought due to security, availability, and performance issue.

MongoDB is perfectly capable of doing 5 collections , as each collection with 10 GB in a single database.

The page MongoDB Limits and Thresholds describes the limits of MongoDB capabilities in detail.

For further your ref here and here


Oracle views not using indexes I am experiencing the unexplainable - at least to me.

I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to make it easier to select data that we need.

Last weekend, we recreated all the tables from scratch. We made sure to recreate all the indexes. However, now I am finding that my views are taking a huge amount of time to complete (from 2 secs to 43 mins). We have gathered stats on the tables using AUTO sample sizes but the explain plan still shows the joins being completed by FULL TABLE SCAN rather than index join.

Does anybody have any ideas? Do you have to do something to force the views to use the indexes.

Thanks Iain

The rebuild of the table and indexes probably caused the statistics about the distribution of the values of certain columns to reset thus causing the optimizer to be fed inaccurate information.

You will probably need to change the statistics gathering for some of the columns.  See Oracle Documentation on Histograms


Stored procedure parameters using tsql functions? Environment sql server 2005 sp3

I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error before @foo. I do not see it can someone help me find it please. Thank you very much.

CREATE PROCEDURE testme
@test AS INT
AS
BEGIN
SELECT @TEST
END

    DECLARE @foo AS CHAR(6)
set @foo = '11test'
EXEC testMe @test = CAST(Substring(@foo,1,2) as int)

Why not do the CAST when you SET @foo instead?

SET @foo = CAST(Substring('11test',1,2) as int)

You could even use a second variable and do this:

SET @foo1 = '11test'
SET @foo2 = CAST(Substring(@foo1,1,2) as int)

and then just call the proc:

EXEC testMe @test = @foo2


Magic number in optimal redo size query in Oracle 9i I got this query from this blog post to determine the optimal online redo log size in pre-Oracle10 databases.

I'm stuck with Oracle 9i for that particular instannce for reasons beyond my pay grade and in this version the view V$INSTANCE_RECOVERY lacks the OPTIMAL_LOGFILE_SIZE column):

SELECT
      (SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
      ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
     (
                SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
                FROM V$ARCHIVED_LOG
                WHERE FIRST_TIME > SYSDATE - 3
                AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
                '00:00' AND '23:59'
      );

I indented the query to understand in better but I fail to understand why the author uses the magic number 20.

Can someone tell me why the 20 and whether this query can be otherwise improved?

First sentence in the blog post:

  The Oracle Database Performance Tuning Guide recommends switching logs
  at most once every twenty minutes.

Official source:

Sizing Redo Log Files

  It is not possible to provide a specific size recommendation for redo
  log files, but redo log files in the range of a hundred megabytes to a
  few gigabytes are considered reasonable. Size your online redo log
  files according to the amount of redo your system generates. A rough
  guide is to switch logs at most once every twenty minutes.


DBCC SHRINKFILE on the distribution database I have an .mdf file from distribution database which is growing.
I know the advantages and disadvantages of shrinking database/files.
Is it okay to shrink the .mdf file to some a smaller size.
Is there any data loss during this time and does this affect the replication.?

It is ok to shink the datafile,  there WON'T be any data loss.

The "issues" when shrinking could be performance, during the rellocation of data pages. Also if you know that your DF will grow again to the same size, shrink is not recommended. If it was because a one time only operation, or other stuff where the DF won't grow at the same big size again, it is ok to perform your shrink operation.


What are the advantages of having a centralised SQL database server for development? I have an idea to use for my fellow developers and basically wanted feedback.

The idea is to have a centralised SQL Server database server for all developers to use.

Each developer would have either own database on the server for the specific project (for example, SW_ProjectName).

Therefore if a developer was off for a week and we needed to hit a deadline we could easily hook into their database by changing our local config connection string.

In addition to this their would be a test database (for example ProjectName_Testing) which the developer would use to run test their sprint before deploying to Staging.

Can anybody think of any advantages and disadvantages for this approach instead of having everybody having their own local databases for development.

I don't know of a single good comprehensive solution to this.

Local development means that developers don't break other people working on their own code against a shared database.  However, when you get latest code, you also need to get the database into the right state to match the code changes you've merged in.  If two people are making changes at the same time, merging can be difficult since database upgrade scripts can be incompatible.  Column order doesn't normally matter in a database, but it can be a bit annoying for databases to be different.

There are good tools to compare schema and data and apply changes.  I would say schemas should aim to be identical.  However, typically you want lookup-type data updated between developers but not regular application data (new customer types, but not new customers).  Configuration data you might want updated, BUT sometimes only a subset (new printer options but not file path settings).

You would think that ideally, you could JUST rebuild your local database completely once you've each merged changes.  If you've set up a bunch of test scenarios through the application for testing (instead of in the build scripts), you now don't have a script to get those changes back into the database.  And this is more difficult as the scope of the database schema increases - with surrogate keys and parent-child relationships which all might have complex dependencies.

In the ideal scenario for a central database, you would have a developer DBA managed the database interfaces for the app and control that so that the exposed interfaces would consistently evolve over time and all developers about that level would see the same interface at the same time.  But then you have two separate groups coordinating their different feature timelines.

Which I think goes a long way to showing why people are still attracted to a variety of other approaches which put more emphasis on code and less on the database.  Ultimately, I think that just shifts the problem around.


Is there a way to limit bandwidth of a backup/restore from a network location? I have moved the location of sql server backups\restores to a file server on the network at an offsite data center.  Unfortunately now my backups/restores for the local reporting server is causing overages to the average bandwidth we pay for from the data center provider per month. Is there a way in sql server to limit bandwidth used for restores/backups?

For clarification, the offsite Data Center is the Production environment for the DB's and I am restoring db's to a local db environment for internal reporting.  DB backups are also being copied to an alternate location for D/R purposes.  The new part in the equation is the restores across the network for internal reporting.  That's what pushed me over my bandwidth limit.

If backups/restores are the only activities occurring between the SQL Server and your offsite Data Center, you could setup Firewall Quality of Service rules to limit transfer speeds.  The total amount of data you transfer can only be reduced by lowering the amount of data you send (such as only sending every other backup).

I will highly suggest that you adjust where your backups are created, though.  You should backup your databases locally (preferably to another server within your local data center or to a separate backup drive locally on the server itself if you don't have other options available), and then configure a job to copy the backups (or every other backup) off site for long-term disaster recovery purposes.  Not only could this help reduce your bandwidth issue, but more importantly, having at least one backup locally will eliminate the possibility of a network outage or some other external factor from preventing you from recovering the database in the event of an emergency.  Backing up a database directly to an external data center is a risky proposition as you can generally count on some sort of network issue every now and again.  I've seen far more network related outages than disk or server outages, though my experience may not be reflective of everyone's.


Receiving error "invalid identifier" while trying to update a table in Oracle I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed.
Tables structure:
create table TEST_FILE_MERGE
(
  customer_num    NUMBER,
  cust_first_name VARCHAR2(180),
  cust_last_name  VARCHAR2(180),
  r_id            NVARCHAR2(1000)
)
create table REQUEST
(
  req_id              NVARCHAR2(1000),
  cust_no             VARCHAR2(50),
  customer_first_name VARCHAR2(50),
  customer_last_name  VARCHAR2(50),
  error_code          NUMBER,
  is_checked          NUMBER(1)
)

Here is my Merge query:
 MERGE INTO (SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID) R
  USING TEST_FILE_MERGE A
  ON (A.CUSTOMER_NUM = R.CUST_NO)
  WHEN MATCHED THEN
    UPDATE
       SET R.CUSTOMER_FIRST_NAME = A.CUST_FIRST_NAME,
           R.CUSTOMER_LAST_NAME  = A.CUST_LAST_NAME,
           R.ERROR_CODE          = 0,
           R.IS_CHECKED          = 1;

And Here is my Update Query which is not correct:
UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME,
          FROM TEST_FILE_MERGE A
         INNER JOIN REQUEST B
            ON A.CUSTOMER_NUM = B.CUST_NO)
   SET B.CUSTOMER_FIRST_NAME  = A.CUST_FIRST_NAME,
       B.CUSTOMER_LAST_NAME   = A.CUST_LAST_NAME,
       B.ERROR_CODE           = 0,
       B.IS_CHECKED           = 1;

COMMIT;

I receive error ORA-00904:"B"."IS_CHECKED" invalid identifier but column IS_CHECKED  exists in REQUEST  table .
What is wrong with my update query?And how am I suppose to put this part of my merge SELECT * FROM REQUEST WHERE REQ_ID = REQUEST_ID into my update statement?
Thanks in advance

Your update query doesn't seem complete or it would give another error message , also it helps to make a [mre].

create table TEST_FILE_MERGE
(
  customer_num    NUMBER,
  CUST_FIRST_NAME VARCHAR2(180),
  CUST_LAST_NAME  VARCHAR2(180),
  r_id            NVARCHAR2(1000)
  ,
  CONSTRAINT cumstom_unique UNIQUE (customer_num)
);

INSERT INTO TEST_FILE_MERGE VALUES( 123,'perter','foe','1')

create table REQUEST
(
  req_id              NVARCHAR2(1000),
  cust_no             VARCHAR2(50),
  customer_first_name VARCHAR2(50),
  customer_last_name  VARCHAR2(50),
  error_code          NUMBER,
  "IS_CHECKED"          NUMBER
);

INSERT INTO REQUEST VALUES (1,123,'Peter','Foe',0,0)

UPDATE (SELECT A.CUST_FIRST_NAME, A.CUST_LAST_NAME
,B.CUSTOMER_FIRST_NAME,B.CUSTOMER_LAST_NAME,B.ERROR_CODE,B.IS_CHECKED
          FROM TEST_FILE_MERGE A
         INNER JOIN REQUEST B
            ON A.CUSTOMER_NUM = B.CUST_NO)
   SET CUSTOMER_FIRST_NAME  = CUST_FIRST_NAME,
       CUSTOMER_LAST_NAME   = CUST_LAST_NAME,
       ERROR_CODE           = 0,
       IS_CHECKED           = 1;

1 rows affected

SELECT * FROM REQUEST

REQ_ID | CUST_NO | CUSTOMER_FIRST_NAME | CUSTOMER_LAST_NAME | ERROR_CODE | IS_CHECKED
:----- | :------ | :------------------ | :----------------- | ---------: | ---------:
1      | 123     | perter              | foe                |          0 |          1

db<>fiddle here
db<>fiddle here