MariaDB Replication - Replicate only specific tables and views Note: A backend developer here with little to no experience in setting up database servers and replication.

What needs to be done

Setup DB replication of an existing database with the following constraints:

Replicate only a specific list of tables/views with different names in the replicated database.
Change the name of the tables/views in the replicated database (during the replication process)
Setup a user on the replicated DB with further restrictions with which only a set of table/view can be viewed/updated/deleted

Progress so far

I have already read the document here, however, I did not find anything concrete to help me move forward with all the use-cases I wish to support!

Use Case

Show only essential data to the external vendor.

PS: If there are any other approaches other than replication, would be happy to consider and implement that as well.

On the master host you have to add to the [mysqld] section of the my.cnf:

. . . . .
binlog_do_db = mydatabase
. . . . .

Master service should be restarted then.
You can rename the replica's database but you can't rename tables, views and other objects. If you want to avoid collisions between local and replicated objects you have to replicate all the objects into the different database.
On the slave host you have to add to the same section:

. . . . .
# DB name mapping (if you need that)
slave.replicate_rewrite_db = mydatabase->mydb
# DB objects filtering - only listed objects will be replicated
slave.replicate_do_table    = mydb.mytable
slave.replicate_do_table    = mydb.myview
. . . . .

Slave service should be restarted too.
Next you have to make an initial copy of the data (remotely):

mysqldump --host 11.22.33.44 -u username -ppassword --master-data=2 \
--quick --hex-blob --add-drop-table --create-options --extended-insert \
--disable-keys --allow-keywords mydatabase mytable myview > mdump.sql

Next you have to create an empty target DB on the slave and fill it by dumped data:

mysql -u username -ppassword -e 'CREATE DATABASE mydb;'
mysql -u username -ppassword mydb < mdump.sql

Then you have to create the slave process on the slave host. mdump.sql file contains binlog filename and position in the header so you can copy and paste them:

mysql -u username -ppassword -e 'CHANGE MASTER "slave" TO \
                                 master_host="11.22.33.44",
                                 master_user="ruser",
                                 master_password="rpass",
                                 master_log_file="filename",
                                 master_log_pos="position";'

Now you can start the slave and check its state:

mysql -p username -ppassword -e 'START SLAVE "slave"; SHOW ALL SLAVES STATUS\G'


Microsoft SQL 2008 Cluster with replication i am in the process of setting upi a SQl Server 2008 64 bit Failover cluster.
A lot of the database queries that we are running during the days are db intensive and lock a lot of the tables while executing.

I was wondering if there is a "best way" to setup a replication server so we can run the queries on it without locking the main db server

If you want to run read-only queries on the replication server - Have you thought about using database mirroring to a reporting server, and then using database snapshots to create a read-only copy?

You'll need SQL Server Enterprise edition for asynchronous replication, and the other SQL Server will needed to be licensed as you're using it. You'd also need to test carefully, and be prepared for some latency.

I assume that you've performance tuned the database intensive queries?


Is there a better way of displaying 'Count' of records beside other columns in a select query? I have a table with below structure :
Test1(c_num  number ,c_first_name varchar2(50), c_last_name varchar2(50))

1)There is a normal index on c_num column.
2)The table has nearly 5 million records.
I have a procedure as you can see below. I want to display the Count(*) along with other columns.I want to know if there are better ways of doing this so that we can have better performance.
create or replace procedure get_members(o_result out sys_refcursor)
  is
begin

open o_result for
  select c_num,
         c_first_name,
         c_last_name,
         (select count(*) from test1) members_cnt -->Is there a better way instead of doing this?
  from test1;

end;

Thanks in advance

What you used there should not cause a problem, because it is a scalar subquery, meaning its result would be automatically cached, and as it has only 1 distinct value, even if test1 has 5 million rows, the inner query would not be executed 5 million times.
But you can use the analytic version of count:
select c_num,
       c_first_name,
       c_last_name,
       count(*) over () as members_cnt
from test1;


Update a table using summed values from another table in MySQL/mariaDB I was trying to use the query below to update the ORDERS table by making the O_TOTALPRICE column up-to-date when the LINEITEM table is modified. And I was using a standard TPC-H database.

UPDATE ORDERS O, LINEITEM L
  SET
    O.O_TOTALPRICE = SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT/100) * (1 + L.L_TAX/100))
  WHERE
    L.L_ORDERKEY = O.O_ORDERKEY;

However, this results in 1111 - Invalid use of group function.

The operation is like this:

-----------------LINEITEM----------------       ---------ORDERS---------
L_ORDERKEY  L_EXTPRICE  L_DISCOUNT  L_TAX       O_ORDERKEY  O_TOTALPRICE
==========  ==========  ==========  =====       ==========  ============
1           12.99       20          8.5     ┬   1           <TO CALC>
1           8.99        30          11      ┘
2           99.99       10          10      ┬   2           <TO CALC>
2           49.99       0           8.5     ┤
2           3.99        50          14.7    ┘

Since there are multiple rows in LINEITEM corresponding to the record in ORDERS, I tried to add GROUP BY L.L_ORDERKEY, but that just came to a syntax error.

Is this possible to accomplish in the UPDATE statement, or does it have to be split to multiple statements? Thanks!!

Assumptions:

L.L_EXTENDEDPRICE should be L.L_EXTPRICE
L_DISCOUNT and L_TAX are percentages, in which case the values stored in LINEITEM need to first be divided by 100.0 before performing calculations

Some sample data:

drop table if exists LINEITEM;
drop table if exists ORDERS;

create table ORDERS
(O_ORDERKEY      int
,O_TOTALPRICE    decimal(10,2));

insert into ORDERS values
(1,0.0),
(2,0.0);

create table LINEITEM
(L_ORDERKEY      int
,L_EXTPRICE      decimal(10,2)
,L_DISCOUNT      decimal(10,2)
,L_TAX           decimal(10,2));

insert into LINEITEM values
(1,12.99,20, 8.5),
(1, 8.99,30,11),
(2,99.99,10,10),
(2,49.99, 0, 8.5),
(2, 3.99,50,14.7);

Contents of ORDERS before the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1          0.00
         2          0.00

Proposed update statement:

update ORDERS O
set    O.O_TOTALPRICE  = (select sum(L.L_EXTPRICE * (1 - L.L_DISCOUNT/100.0) * (1 + L.L_TAX/100.0))
                         from   LINEITEM L
                         where  L.L_ORDERKEY = O.O_ORDERKEY);

Contents of ORDERS after the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1         18.26
         2        155.52

Here's a rextester fiddle of the above.


Is there any way to find the number of pages read by each operator in query plan? I need to find number of pages scanned by the operator in a sql-server query plan.
I used

SET STATISTICS IO ON

This returned number of logical, physical, read-ahead pages scanned per table,
but I need for every operator.

Moreover, I am unable to read the IO messages using JDBC  driver programs, and
I have more than 100 queries to be executed and to record number of pages read by each operator.

Is there any method to at least get number of pages per table, that can be accessed by JDBC driver programs

OR

Is there any flag kind of thing to be set to get the number of pages scanned in the XML PLAN Itself.

Io statistics per table was added to actual execution plans starting in SQL Server 2016. It was ported to SQL Server 2014 SP2 as well.
I executed a simple query and uploaded the query plan here. Comparing the results of SET STATISTICS IO ON:

Table 'BIG_TABLE'. Scan count 1, logical reads 921659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SMALL_TABLE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To what's in the XML for the actual plan:
<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="6451600" ActualRowsRead="6451600" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="972" ActualCPUms="972" ActualScans="1" ActualLogicalReads="921659" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[BIG_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>

<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualRowsRead="10" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[SMALL_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>

Reveals a perfect match in this case.


SSIS Multicast performance question I'm wondering how multicast actually works. So here is my setup. I have one source flat file(csv) of about 100k rows, maybe about 250-300 columns, and I'm multicasting that file into 7 one-to-one tables. Only the primary key column is duplicated among these tables. This setup seemed to work fine, but now it seems we're maybe running into memory issues. I'm wondering if multicasting that data to 7 destinations is too much or not. Any thoughts on memory usage?  I don't really care how fast this runs, just looking to reduce memory usage.

One alternative I was thinking of was maybe just creating 7 different connection managers for the same file, and running everything sequentially. Would that be better? Thanks!

SSIS gets its power by being an in-memory transformation engine. The base unit of work within a data flow task is the buffer. If you ever wonder why SSIS is so persnickety about data types, it's because it calculates the cost for a row and then allocates memory for N rows. All* the downstream components use the same memory address to do their part of the ETL, which allows for parallelization. This means that the cost for a multicast with 1 output is the same as a multicast with 10 outputs. It simply allows multiple components to consume the data.

Where I'm not 100% certain on is a situation where pre-multicast, I have column called SSN. In output 1 from the MC, I rot-5 the digits in place and write to a destination and output 2 with the unaltered version, there has to be an additional memory cost associated with that but I assume it's just the space required for the column to be duplicated.

*All the downstream components use the same memory address until you introduce asynchronous components (Sort, aggregate, etc). When those hit the data flow, then that buffer ends and the data is copied from memory space 1 into memory space 2. Which sounds good from the perspective of "I just reduced the number of columns from 100 to 10" but you've fractured your total memory space between "before async component" and "after async". Instead of being able to run 100 buffers at once, you're at 30 before and 70 after and that memory copy in between is an expensive operation.

If you want to reduce memory usage, architect your packages to use less ;)

You can use less by avoiding the asynchronous transformations.
Only pull in the columns you need.
If you have a lookup, write a query explicitly enumerating the needed columns instead of clicking the table in the drop down list.
If you have a lookup, look at restricting the depth of the rows returned. If you only need to lookup current values, in the query above, make it WHERE ISCurrent = CAST(1 AS bit);
Watch your data types. Importing from a file, don't be lazy and start with strings only to convert to a data type later (unless you have questionable data cleanliness)
Watch your data types too! It's unlikely you're dealing with a binary/LOB data type coming from a flat file but those data types can't fit in memory with any certainty so SSIS will carry a pointer in memory to an address on disk. While this doesn't eat all your memory, it does slow down processing as many tiny files are created and destroyed during the operation


Fillfactor is ignored when non-clustered index added I have a test scenario where I have added fill-factor = 90 to a clustered index over a random generated GUID primary key.

If I create the table without fill-factor = 90 then the number of page splits is initially 31 for 3974 rows and splits occur with every subsequent insert. When I include the fill-factor = 90 the number of splits on population rises to 35 but splits stop happening with every subsequent insert. All good so far!

The problem is that when I add a non-clustered index although the clustered index still indicates a fill-factor of 90% the initial insert is back to 31 splits and splits are happening again on every subsequent insert indicating that the fill-factor is being ignored.

Does anyone have an idea as to why this might be happening?

    /*
    scenarios:
    1. Create table #Test02 without fillfactor, populate and observe 37 page splits occured, insert two rows at a time and observe page splits occurring
    2. Drop and create table #Test02 with filfactor = 90, populate and observe 40 page splits occurred, insert two rows at a time and observe that page splits do not occurr
    3. Drop and create table #Test02 with filfactor = 90, insert two rows then populate, observer 38 page splits occurred, insert two rows at a time and observe page splits occurring
    4. Drop and create table #Test02 with filfactor = 90, create index IX_#Test02_ProductName, populate and observe 37 & 35 page splits occurred, insert two rows at a time and observe that page splits occur on PK
    5. Rebuild index PK_Test02, insert two rows at a time and observe that page splits do not occurr

    Conclusion: On an empty table with a PK FF = 90 SQL preserves the fill factor
    */

    --non-sequential index table
    IF OBJECT_ID('tempdb.dbo.#Test02') IS NOT NULL DROP TABLE #Test02
    --CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID))
    CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID) WITH (FILLFACTOR = 90))

    --ALTER INDEX PK_Test02 ON #Test02 REBUILD WITH (FILLFACTOR = 90);

    IF EXISTS (SELECT * FROM tempdb.sys.indexes WHERE name = N'IX_#Test02_ProductName') DROP INDEX IX_#Test02_ProductName ON #Test02
    CREATE INDEX IX_#Test02_ProductName ON #Test02(ProductName)

    --populate script
    INSERT #Test02(ProductName)
    SELECT TOP 4000 COALESCE(O1.name,O2.name)
      FROM master.sys.objects O1
CROSS JOIN master.sys.objects O2

    --two row insert
    INSERT #Test02(ProductName) VALUES(N'Straight Banana'),(N'Bent Banana')

    --observe page splits
    SELECT ios.index_id
         , o.name as object_name
         , i.name as index_name
         , ios.leaf_allocation_count as page_split_for_index
         , ios.nonleaf_allocation_count page_allocation_caused_by_pagesplit
         , ios.leaf_insert_count
         , i.fill_factor
      FROM tempdb.sys.dm_db_index_operational_stats(db_id(N'db_name'), null, null, null) ios
      JOIN tempdb.sys.indexes i on ios.index_id = i.index_id AND ios.object_id = i.object_id
      JOIN tempdb.sys.objects o on ios.object_id = o.object_id
     WHERE o.type_desc = N'user_table'
       AND o.name like N'#test02%'

Fillfactor can be set once over the server (so it will be used in all newly created indexes unless another FF is explicitly specified in CREATE INDEX statement) or it can be defined individually for each index.

So if you want your non-clustered index to have FF = 90 just specify it when creating it.

FF of clustered index has no relation to FF of non-clustered indexes defined over that clustered index.

In other words, when creating an index, the specified FF is used. If nothing is specified, server default FF is used. In your case server default FF is 100%.

............................................................................

It seems I understood your question only now.
You are asking about why don't they maintain FF when INSERTs happen to your table.

Here is documentation:

Specify Fill Factor for an Index

  The fill-factor option is provided for fine-tuning index data storage
  and performance. When an index is created or rebuilt, the fill-factor
  value determines the percentage of space on each leaf-level page to be
  filled with data, reserving the remainder on each page as free space
  for future growth. For example, specifying a fill-factor value of 80
  means that 20 percent of each leaf-level page will be left empty,
  providing space for index expansion as data is added to the underlying
  table. The empty space is reserved between the index rows rather than
  at the end of the index.

So, fillfactor (the percentage of fullness of the leaf level) is maintained only when you create or recreate your index. When you subsequently use your table (make INSERTs or UPDATEs) your index leaf level becomes full. It's normal, It does not depend on non-clustered index existence, it's just how it should be. The empty space created at index creation time serves to mitigate page splits, but if you want to maintain it just to maintain empty space it just has no sense.

UPDATE

  It would seem that if an empty table only has a clustered index then
  SQL will maintain fill factor on initial population but not if it has
  a non-clustered index as well.

You still do not understand how FF works.
When you define it on an index, i.e. the index is created, the FF is respected.
This index will be populated with this FF in mind.
The next time it will be respected is only when the index is rebuilt.

So when you define FF on a table containing rows, these rows will be put in index with respect of the FF defined.

If you define the FF in an index created on empty table, nothing happens, there are no rows, how can you leave empty space between them?

I wrote a small repro where you can clearly see that no FF is maintained when you define it in the index created on an empty table.

This does not depend on any non-clustered index, it's just how FF works:

create table dbo.nums_guid (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid(id) with (fillfactor = 50);

insert into dbo.nums_guid (id, n)
select newid(), n
from s1057.dbo.nums;

create table dbo.nums_guid_1 (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid_1(id) with (fillfactor = 50);
create unique nonclustered index ix_n on dbo.nums_guid_1(n);

insert into dbo.nums_guid_1 (id, n)
select newid(), n
from s1057.dbo.nums;

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

There are 2 tables in my repro, both with FF = 50 defined: one has a clustered index only and another clustered + non-clustered indexes.
In both cases clustered index is defined on empty table.
When I do my INSERT, no FF is maintained:

And only after rebuild I'll have FF = 50 on both clustered (and still it does not depend on existence of non-clustered index):

--------
alter index ix_id on dbo.nums_guid rebuild;
alter index ix_id on dbo.nums_guid_1 rebuild;

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

select object_name( object_id) as tbl_name,
       object_id,
       index_id,
       page_count,
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0


How to safely replace the old table with its cleaned copy? I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with
DROP TABLE table;
ALTER TABLE table_cleaned rename to table;

but the first statement gives cannot drop table cals_status because other objects depend on it.
I definitely don't want to use cascade as it would delete all the dependent rows.
How can I safely replace the old table with its cleaned copy?
Following Erwin Brandstetter's advice, this is the missing detail of the error message:

DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq

By default, the error message tells you more. Like:

ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE to drop the dependent objects too.

General solution
Include the object mentioned in the Detail line in your script.
Also, to do it safely, wrap all of it in a transaction, and write-lock the old table before working on the new to avoid losing work done during the transition:
BEGIN;

LOCK TABLE tbl IN SHARE MODE;

-- prepare tbl_cleaned

DROP VIEW v_tbl;  -- or any other depending object
-- more?

DROP TABLE tbl;
ALTER TABLE tbl_cleaned rename to tbl;

CREATE VIEW v_tbl AS SELECT  ... -- actual definition, see below
-- more?

COMMIT;

There may be more depending objects. Same procedure.
In the example, to get the view definition:
SELECT 'CREATE VIEW v_foo1 AS '
    || pg_get_viewdef('v_foo1'::regclass) AS view_definition;

See:

How to list the columns of a view in Postgres?

And you may want to rename constraints and indices to fit the renamed table - or hold back on those and create with correct names after renaming the new table. (Again, be sure to get the definition before dropping the old table!)
Related:

Best way to populate a new column in a large table?
How do I replace a table in Postgres?

If there are many depending objects and the table is not too big, a good alternative might be to prepare the new table (possibly as temporary table for better performance), then TRUNCATE the original and INSERT from the prepared new table. This way, depending objects like views, FK constraints, rules, function using the table type etc. can just stay in place. See:

Add new column without table lock?

Or you might be able to update the old table in place? This related answer shows code for all three variants:

Compacting a sequence in PostgreSQL

Specific solution
Your particular error message:
DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq

... indicates that you are (ab-)using a sequence to generate default values for multiple tables. Or at least it's owned by the wrong column. The solution depends on how you want to deal with the situation.
Or you created the new table copying the complete structure including default values depending on the same sequence.
Typically, you want a dedicated sequence. So you should probably make the new column own the SEQUENCE:
ALTER SEQUENCE cals_status_id_seq OWNED BY tbl_cleaned.id;  -- column in new table *before* switching

DROP TABLE only cascades to the sequence if it's owned by one of the columns in the table to be dropped.
Related:

How to reset Postgres' primary key sequence when it falls out of sync?

You may be interested in IDENTITY columns which avoid this kind of complication with serial columns. See:

Auto increment table column


Is a functional dependency on a "child" table allowed in third normal form (3NF)? Consider two simple tables, OrderHeader and OrderDetail which contain, respectively, the data shown below:

OrderHeader

| OrderHeaderID | CustomerID | OrderTotal |
|---------------|------------|------------|
| 1             | 124        | 14.5       |
| 2             | 525        | 35.6       |
| etc           |            |            |

OrderDetail

| OrderDetailID | OrderHeaderID | ProductID |
|---------------|---------------|-----------|
| 1             | 1             | 415       |
| 2             | 1             | 52        |
| etc           |               |           |

The OrderTotal column can be calculated by adding the price of each product from the OrderDetail table.  That sounds like a functional dependency! ie:

SUM(OrderDetail.Product.Price) → OrderHeader.OrderTotal

...but afaik it's not a violation of 3NF.

Is this actually allowed?  If yes, is it disallowed in higher normal forms?

There is an informal dependency, but it's not a functional dependency.  See the wiki article for the formal definition. Here's an excerpt in simple terms:

  In simple words, if the values for the X attributes are known (say
  they are x), then the values for the Y attributes corresponding to x
  can be determined by looking them up in any tuple of R containing x.

There is no normal form definition which precludes summarizing child record values in the parent record.  However, lots of people would suggest against doing this in transactional databases, particularly where there's a possibility that a child record could be updated (or deleted, or more child records added) such that there's a risk of the attribute in the parent becoming inconsistent with the aggregate value of the children.

If your OrderHeader table had a CustomerName column, then that would violate 3NF because of the transitive functional dependency on CustomerID.

Being informal, a lot of people would say that OrderTotal is denormalized because it's susceptible to things that look like insert, update and delete anomalies and so people draw a comparison with the formal definitions of normalization and normal forms (since these are also about avoiding insert/update/delete anomalies).  There are some sticklers, though, who will be adamant that avoiding summary attributes in parent records is not the same thing as normalization per se.


Trigger to automatically add values to a table in Oracle I am new to Oracle.

I am looking for a trigger which helps to automatically insert values into a table when data is entered into another table in the same database.

I have tried a lot and didn't succeed. Can anyone explain how this is done?

Sounds like you just want a basic AFTER INSERT trigger. Then you can access the inserted data via :new. Something like:

CREATE OR REPLACE TRIGGER insert_data
AFTER INSERT
ON OWNER.TABLE
FOR EACH ROW
BEGIN
INSERT INTO OWNER.OTHER_TABLE
(COL1,
COL2,
COL3)
VALUES
(:NEW.COL1,
:NEW,COL2,
:NEW,COL3)
END;


Some dates replicated with a +4 offset I've set up replication between an EC2 instance as master and a MySQL 5.5 RDS instance as slave. Everything is (mostly) fine except for one odd thing: the dates in a transaction table are either correct or have a +4 offset and the only correlation I can find is in the application code.

Details:

EC2 Master is running MySQL 5.5 with time_zone = SYSTEM. The 'date' command shows the master is in EDT (US/Eastern).

RDS Slave is running MySQL 5.5 with time_zone = US/Eastern.

binlog_format is MIXED.

The transaction records are written from two different points in the code because the data being transacted comes from two different vendors, A and B. They are written to the same transactions table. The datatype of field, create_time, is TIME.

When processing Vendor A's data, the application uses

$create_time = Carbon::now()->toTimeString()

to generate the time. The time gets saved to the database and properly replicated to the slave.

When processing Vendor B's data, the application uses NOW() in the SQL statement for create_time. The time gets saved to the master database properly, but is replicated with a +4 hour offset.

I don't understand how correct data in the master is replicated differently based on the application code; once the correct data is written to disk, it's written to disk and that's what is replicated, right?

Musings:

I wonder if this bug is applicable to my situation.

BTW, I have some other tables where ALL of the dates are either correct or have a +4 offset. No correlations in the application code found there. Yet.

My suspicion is it's the bin_log format. I'm about to dig through the binlogs to see what I can find. If it is, do I need to tear down my replication and start from scratch?

Edit:

After replication synced up, I ran pt-table-checksum on all databases. No errors were found.

So, yes, the problem was the time_zone settings.

As mentioned in the docs, if time_zone=SYSTEM, MySQL will do a timezone calculation, otherwise, it (presumably) will not.

When you INSERT/UPDATE the data as a string, MySQL stores it "as is". If you INSERT/UPDATE the data with NOW(), it stores the current UTC value.

Since my slave had time_zone=US/Eastern, it never did the timezone calculation, so some of my dates were off by +4 hours.

This seems to have fixed the problem of dates in other tables as well.

That's my story and I'm sticking to it. :-)


UOW's: Relating UOW's listed in MSDTC to SQL Server info We have some active transactions showing up in MSDTC that have been there for a very long time and don't go away. They're not orphaned or in-doubt, just normal-looking active transactions.

We're trying to figure out what they are and why they're there.

To that end, I'm trying to find a way to map one of these active transactions that are being presented in MSDTC Transaction List (Component Services -> My Computer -> Distributed Transaction Coordinator -> Transaction List) to any information at all in MS SQL Server.

Obviously, all I have to work with is a UOW ID.

After several hours of research, I can't find anything outside of sys.dm_tran_locks that would have this info.  The problem I'm having with sys.dm_tran_locks is that my transactions do not show up, presumably because they don't currently hold any locks.

Other important info:
SQL Server 2005. (Yes, 2005 :(  )
Again, nothing is "orphaned" or "in doubt". The transactions just show as active, but they stay there forever.
There is no clustering or anything else exotic. It's all very plain-vanilla SQL Server 2005 standard edition.

EDIT: I should also note that I tried:

* SP_WhoIsActive (both with default settings and with more info gathering turned on)
* sys.dm_tran_locks
* sys.dm_exec_sessions
* sys.dm_exec_requests.
* sys.dm_tran_database_transactions
* sys.dm_tran_database_transactions
* sys.dm_tran_active_transactions

I'm beginning to think you just can't get there from here ;-)
Again, I want to associated a UOW in MSDTC to a UOW in SQL Server where, apparently, the distributed transaction is still active but holding no locks.

As requested, here is a screenshot of MSDTC:

You can get the correlation you're looking for in the sys.dm_tran_active_transactions DMV.
The column is transaction_uow.

http://msdn.microsoft.com/en-us/library/ms174302.aspx


Postgresql: type_modifier in UDT In the manual for creating UDTs in postgresql I found the following statement:

  PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this information must be capable of being packed into a single non-negative integer value for storage in the system catalogs.

So is there really no way, to store more information that would fit in a non-negative integer? I would like e.g. to be able to store the name of a function that should be called as a default upon insertion on the data (since there might be multiple functions that could work on that data). That is not possible?

The system catalog for types, pg_catalog.pg_type, has the following column:

typtypmod integer not null

as you can see by running \d pg_type.

There is no way to store something you can't fit into an integer because that's how the catalog stores typmods.

The non-negative requirement appears to stem from the use of -1 as reserved value.

The specific problem you describe sounds better suited to the addition of a column trigger. It sounds like what you really want is DOMAINs and the ability to add triggers on domains - but unfortunately at this point there is no support for triggers on domains.


Optimize SSIS package for fewer queries I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order:

CRM server::CRM db::CRM table
----------
CustomerID   PreviousLevel    NewLevel    NewLevelGrantedOn

Order server::Order db::Order table
----------
OrderID     CustomerID    OrderPlacedOn

In an SSIS package I did this:

Execute SQL against Order db to extract the orders and put them in an object variable;
Loop through each order using "foreach container", in which I put a data flow task select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn desc that extract data from CRM db (both parameters come from step one), derive some columns and write the output to another table.

There are more then twenty thousand records in the order db, which means the data flow task will be executed for more than twenty thousand times. The CRM db will be queried for more than twenty thousand times, too. It takes more than an hour to do these.

Can I utilize some built-in features to speed up these (or do it in a "smart" way)? And, is an hour a long time, in the context of ETL and / or SSIS?

You can achieve this without using Foreach enumerator, just use one data flow task to achieve this.

Building the Package

First of all, add a Data Flow Task to the control Flow

1. OLEDB Sources

In the DataFlow task add an OLEDB Source that Read from Orders Table (the same command used in the Execute SQL Task (first step in your question)

Select * FROM Order

Also add a second OLEDB Source that read from customer table:

select * FROM CRM

2. Sort

After each OLEDB Source add a source component:

The First (order table) you have to select CustomerID and OrderID columns for sorting and select the sort type as ascending
The Second (customer table) you have to select CustomerID (sort type
= ascending) and LevelGrantedOn (sort type
= descending) columns for sorting

3. Merge Join

Add a merge Join component to join both sorted outputs. and select CustomerID column from both outputs as Join Key and select the output columns you need from both tables

4. Conditional Split

After merge Join add a conditional split to filter rows only that match the following expression

[LevelGrantedOn] < [orderdate]

5. Script Component

The final component we have to use is a script component to get only the first row for each customerID (because both source are well sorted just grabbing the first row is similar for Select top 1 ... ORDER BY LevelGrantedOn desc)

In the script component add an Output Column OutFlag of type DT_BOOL and Use the following script:

This script will set OutFlag to True when the customerID occur for the first time (similar to TOP 1)

    Public Class ScriptMain
        Inherits UserComponent

        Dim lstCustomerID As New System.Collections.Generic.List(Of Integer)
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            If lstCustomerID.Contains(Row.ID) Then

                Row.OutFlag = False
            Else

                lstCustomerID.Add(Row.ID)
                Row.OutFlag = True

            End If

        End Sub

    End Class

6. Conditional Split

Add a second Conditional Split to filter Top 1 rows:

[OutFlag] == True

7.OLEDB Destination

Connect the Conditional Split Output top the OLEDB Destination and map the columns.

The Dataflow task must looks like


Which performance can I expect? I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1.

I'm trying to get a feel for how fast/slow my setup is, and am running pgbench. This is the command I've been running to initialize pgbench:

pgbench -i -s 100 -U jboss -p 5432 -h 127.0.0.1 project2_core

The first 5000000 tuples run by very quickly, but then it gets slower and slower. Now, it displayed

10000000 tuples done.

for a very long time (10 minutes or so) with no CPU load, and also RAM is not an issue. Only then, it continued with

set primary key...

and some NOTICEs about primary keys.

My question: is this runtime to be expected, or must I suspect there's something wrong with my system?

I'd look at how you have your IO stack set up, which you say nothing about in the question. It sounds like actually committing the rows is taking a really long time.  Virtual file systems on top of other file systems often display the worst characteristics of both layers.

You also should try doing an actual test run, as well as just populating a database.


Patching strategy when Windows and SQL Server patches not applied for over 3 years The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the installation in late 2012. They are using synchronized AGs with auto-failover for redundancy so we can upgrade one node at a time.

The plan is to disable auto-failover, update the second node, failover the databases then update the first node.

Question: How do you recommend approaching this? Apply 3 years of DB and OS patches all at once to the second node, then failover to that node?
How much out of sync can the nodes be and still expect a trouble-free failover of AG databases? Is it better to do the Windows update first, or the SQL CU?

The goal is to minimize downtime and the number of failover/failbacks.

IMHO I'd recommend breaking up the updates into major SPs and OS vs SQL. That way if anything breaks you can rollback more easily and pinpoint issues. For example, roll out Server 2012 SP1 let it mature for a week or two, then do a SQL SP wait a week or two, etc...

Your methodology sounds good for patching the secondary node, failover, patch primary node, and then balance the cluster.


Testing stored procedure scalability I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.

I'm trying to slam the DB to see what the breaking point (# of requests per second) would be.

In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app.

How can I make this a good test to achieve the test results based on SQL performance?

Is there a tool for this that i can give it a stored proc name and param for it to pund my  DB?

I want to see if the DB can return a min. of 250 responses per second.

SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities.

A very brief outline of the tools:

ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
OStress is used to replay .rml and .sql files against a server.
ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
Reporter provides performance analysis reports.

There is an excellent fast start article from the SQLCat team which includes a sample database and workloads, Precision Performance for Microsoft SQL Server using RML Utilities 9.0

You have the option of either Profiling activity for replay or, possibly more appropriate in your scenario, hand cranking a set of .sql scripts that you then replay via OStress and ORCA.

Great tools for both load testing and investigating performance issues.


How can I delete rows in an oracle table from xml input parameter? > CREATE OR REPLACE PROCEDURE PRC_OPEN_PERIOD
>     ( InputXml in VARCHAR2,Tmode in varchar2,pOUT OUT number )
>     IS
>      v_count NUMBER(38) := 1;
>      v_inner NUMBER(38) := 1;
>      str varchar(20);
>      l_xml xmltype;
>     BEGIN
>      IF Tmode ='DELETE' THEN
       l_xml=xmltype(InputXml);
>      DELETE FROM TBL_MST_OPEN_PERIOD
>       WHERE month = l_xml.extract('//openperiod/month/text()').getStringVal()
>       and
>       village_id in (select village_id from tbl_mst_open_period
>       where
>       village_id in
>         l_xml.extract('//openperiod/village/villageID/text()').getStringVal())
> ;
>      pOUT :=1;
>      COMMIT;    END IF;   END;

STRUCTURE OF THE INPUT XML:

<root>
<openperiod>
<month></month>
<village>
   <villageID></villageID>
   <villageID></villageID>
</village>
</openperiod>
</root>

I have a table named tbl_mst_open_period with columns period_id number, month number, village_id number, created_by varchar(20), is_active varchar(1).

I want to delete rows from the table by extracting the values from the xml input parameter. But the data is not getting deleted. Can anyone help??

P.S. When i replace the extarct function by hardcoded values, the delete is working. Also I tried the same xml in an insert into the table by extracting. It was working then.
Also is the delete statement actually correct??

The problem is your village subquery, it returns a single value of aggregated village ids instead of a table of ids:

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT extract(l_xml,'//openperiod/village/villageID/text()').getStringVal()
 13    FROM DATA;
EXTRACT(L_XML,'//OPENPERIOD/VI
--------------------------------------------------------------------------------
12

Instead, you should use XMLTABLE (see this other SO question for example):

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT x.id
 13    FROM DATA d,
 14         XMLTABLE('//openperiod/village/villageID'
 15                  PASSING d.l_xml
 16                  COLUMNS ID VARCHAR2(30) PATH 'text()') x;
ID
------------------------------
1
2

My advice when dealing with complex queries:

start with SQL, then when your query is sound and gives the good result, put it in PL/SQL
start with SELECT, not DML
decompose your query, make sure each subquery returns the appropriate result
build a small example with test tables with 1-2 rows each


Bulk table creation and import of data I have a around 400 CSV files which have been exported from an old Paradox database, with each CSV representing a table within the old database.  Each CSV has a different number of columns and different column headings.

I'm looking for suggestions on an easy way to bulk import all of these files into SQL Server, with a new table created for each of the CSV files.  No relational structure is required.

What options do I have for quickly getting creating the 400 tables and importing the data?

A fairly easy option I like is just using PowerShell.

Get the out-datatable.ps1 module from CodePlex here and loop through your .csv files.

Then generate a SQL statement to create your table and use SQL bulk insert to load your data into your table.

This is a script that could do what  you want, it processes .txt files as tab-delimited and csv files as comma-delimited.

You may need to adapt it a bit for your needs, it's a script I used somewhere and tried to clean up before posting:

$ErrorActionPreference = "Stop"
########################################
#                                      #
#  Define paths and connectionstring   #
#                                      #
########################################

$ConnectionString = "Data Source=localhost; Database=dbname; Trusted_Connection=True;";
$ScriptDirectory = "C:\\scriptdirectory"
$FilePath = "C:\\import\\csv\\"

######################
#                    #
#  Import CSV Files  #
#                    #
######################

# Local variables
$CreateStatement = ""

# Go to working directory
Set-Location $ScriptDirectory
# Source script from https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
. ".\Out-DataTable.ps1"

# Process files recursively
$Files = Get-ChildItem -Include @("*.txt","*.csv") -rec $FilePath | Where-object {!$_.psIsContainer -eq $true}
foreach ($File in $Files)
{
    $TableName = $File.BaseName
    # Read in CSV
    if ($File.Extension -eq ".txt")
    {
        $csvDataTable = Import-CSV -Delimiter "`t"  -Path $TempFileName | Out-DataTable
        Remove-Item $TempFileName
    }
    if ($File.Extension -eq ".csv")
    {
        $csvDataTable = Import-CSV -Delimiter ";"  -Path $File.FullName | Out-DataTable
    }
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
    $bulkCopy.DestinationTableName = "[" + $TableName + "]"
    # If datatable exists, create table & import datatable
    if ($csvDataTable -ne $null)
    {
        # Create Table
        $CreateStatement = "CREATE TABLE [" + $TableName + "] ("
        foreach($Column in $csvDataTable.Columns)
        {
            $CreateStatement += "[" + $Column.ColumnName + "]  NVARCHAR(MAX),"
        }
        # remove last column & close parentheses
        $CreateStatement = $CreateStatement.Substring(0,$CreateStatement.Length-1) + ")"
        $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString);
        $cmd = new-object system.data.sqlclient.sqlcommand($CreateStatement, $Connection);
        $Connection.Open();
        if ($cmd.ExecuteNonQuery() -ne -1)
        {
            Throw "Create Table failed"
        }
        $Connection.Close();
        $bulkCopy.WriteToServer($csvDataTable)
    }
}

It does have an issue with creating every column as NVARCHAR, that may not be suitable for your needs, but the datatable doesn't detect the data type unfortunately, if you figure that out let me know how you did it.


SQL Table Indexing - Database Overloaded With Queries I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour.

I'm trying to optimise our database and I noticed this in our database runtime information:

  Select_full_join  789  The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Obviously that is far from optimal. And other red flags are showing too which are probably related to this root problem I believe.

Now, we have a database structure that is not exactly well indexed. We have a unsigned INT as primary key on our tables and then just unindexed columns.

I wonder how I should approach this. Do I need to add indexes to every column? Would that even help? How should I approach this problem?

There are several approaches that can help.

If you just throw in more indexes, then you are likely to speed up most simple queries, as well as introduce unused indexes. Unused indexes don't really hurt if the database is read-mostly, but they take up space. Unless you care about disk space, adding single-column indexes is the fastest way to speed up table reads but also slow down table writes. Knowing the architecture, however, does help avoiding useless indexes.

You can enable logging slow queries or queries that don't use indexes:

SET GLOBAL log_queries_not_using_indexes 'on';
SET GLOBAL slow_query_log 'on';
SET GLOBAL log_output 'table';

Wait a little for the log to fill up, then query the log with

SELECT * FROM mysql.slow_log;

This should point to potential new indexes. Note that every language feature that is available for normal queries is also available here, so use what you need. Once you create a new index, you can reset and repopulate the log: TRUNCATE mysql.slow_log;

Also read up [here] for the details of slow query logging.


Need help with this sql query for Postgres DB for a Python flask application @app.route("/home/search/details/<string:isbn_result_isbn>")
def book_search_results(isbn_result_isbn):
    book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()
    book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=:v",{"v": book_details.id}).fetchall()
    return (f"{book_id}")

Unfortunately book_id is returned as blank.
However if the query is hard coded:
(book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=89).fetchall())

I get a result. I know the problem is with book_details.id (that being an integer but is cast as a string in the sql query & hence I get no result), but can't seem to figure a way out of this.
Kindly help

I think there are several problems with your method. The first question would be: do you only want to return book_id for books that have a review? In your code:
book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()

If not mistaken, book_details is a tuple like:
(col1, col2, ...)

Second, why bother looking for the book_id in book_reviews if you already know what it is?
If you want to return the book_id for a particular ISBN, then:
book_id = db.execute("SELECT book_id FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()[0]

is sufficient. If you want to return the book_id only if it has been reviewed:
book_id = db.execute("SELECT book_id FROM books b WHERE isbn=:g and exists (select 1 from book_reviews r WHERE r.book_id=b.book_id)", {"g": isbn_result_isbn}).fetchone()[0]

One of the really nice things about python is multiline strings, so you could write it like:
sql = """SELECT book_id
         FROM books b
         WHERE isbn=:g
           AND EXISTS (
               SELECT 1
               FROM book_reviews r
               WHERE r.book_id=b.book_id
         )"""
book_id = db.execute(sql, {"g": isbn_result_isbn}).fetchone()[0]


Restructure create and delete events on rows into single table Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log.

I have table that logs 'create' and 'delete' events for a resource that corresponds to an object.

Every event:

Is it's own row
Has a unique ID
Has a timestamp of when it occurred.

-------------------------------------
| id   | type   |object| timestamp |
-------------------------------------
| 1    | create | A    | 2019-1-1  |
| 2    | create | A    | 2019-1-2  |
| 3    | delete | A    | 2019-1-2  |
| 4    | create | B    | 2019-1-1  |
| 5    | delete | B    | 2019-1-2  |
| 6    | create | A    | 2019-1-3  |
| 7    | create | B    | 2019-1-4  |
| 8    | create | C    | 2019-1-5  |
-------------------------------------

The question I want to answer is how many resources still exist for a given object.

Example:

According to the data in the sample table, Object 'A' still has 2 resources that exist (three where created, one  was deleted.).  Object B and C each have one remaining resource.

Is it possible (as in the linked problem) to restructure this in a view where I have create and delete columns with timestamps?

No restructure is needed.  (Or I don't understand why there might need to be a restructure.)

SELECT  object,
        SUM(type = 'create') - SUM(type = 'delete') AS remaining_ct
    FROM tbl
    GROUP BY object;

This assumes that 'creates' occur before 'deletes'.


Copying a DB or tables from a local Postgres database to Heroku I'm almost a novice with databases.
I created a PostgreSQL database with the python programming language.
I am pleased to note that the database was created as I wished. I could easily verify it from pgAdmin 4, the database administrator installed locally. But now, since it is ready to be used, I would like to upload it to Heroku in order to work with it inside my web application.
I followed the various steps slavishly on the site of Heroku, but I can not complete the various steps, especially at the following command:

set DATABASE_URL=postgres://$(whoami)

I wonder if it's an operating system problem, in the sense that the steps listed on the Heroku site could be os-agnostic and not addressed to Windows, the system I'm working on. I also believe that some commands are directed to connect the database that is located locally, not to migrate on the domain of Heroku.

First, take a dump of the db under Windows as outlined here. This will be a human readable text file - worth a look to get a feel for what's going on "under the hood", i.e. in the background!

Then, ftp that file (as text) over to the Heroku box (Linux I presume) and do a restore from the dump as outlined here.

psql dbname < infile

You may have to create an empty schema - can't remember.

Then use psql to connect to your new db.

p.s. a word to the wise - if you're going to be using Python, Heroku and PostgreSQL, then you should be using a Linux box as your main machine - all three are Linux "natives" rather than Windows. Try it out - you'll wonder how you ever did things any other way!


what is the best practice when making changes to the the application and database? I need to make changes to an application which utilizes a database that is replicated across locations. I was wondering what the best practice is when working with databases and making changes to the database.

I will be backing up the database to dev environment and making changes to both the application and the database. Then when testing the application and the database I will likely make changes to the records. In that case, would it make sense run the t-sqls that I applied in the dev to the production database?

That's a big topic. The tactics depend in part on how big a change you're making. The strategy is that every move to production breaks nothing. Loosely speaking, that means

you add "new things" to the database,
alter all application code to use the new things instead of the old things,
then, if prudent, remove the old things.

That equates to one to three moves to production. (I prefer three, with a prudent amount of time between them. Other developers might have more appetite for risk than I do.)

The most accessible online document might be Evolutionary/Agile Database Best Practices. It's relevant to some extent regardless of whether you follow agile practices. Look at Database refactoring and Agile data modeling first.

  would it make sense run the t-sqls that I applied in the dev to the production database?

That depends on what kinds of problems you uncovered, how you documented them, and whether the TSQL code is under version control. Writing TSQL is software development; all the principles of good software development apply.


SQL Server 2016 DB is SUSPECT - cannot do anything with it! User DB is in SUSPECT state.

sp_who2 shows that is isn't being used in any SPID.

I cannot ALTER to SINGLE_USER.  I have a FULL+DIFF backup combo that I restore from, but without exclusive access, I cannot restore.

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.

Tried to DROP, having closed connections - no joy.

Tried RESTRICTED_USER just in case - no joy.

Am I missing something really obvious, or do I need to take more drastic action?  (Such as restarting SQL with everyone disconnected from every SSMS on the planet... (developers on multiple continents))

Any assistance is hoped for...

Thanks!

Addendum

I should add that the transaction log filled up the disk.

All attempts to backup the existing TLog fail due to SUSPECT presence.

The SQL Error Log message, "During undoing of a logged operation in database 'MyDB', an error occurred at log record ID (541478:273336:241). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database." proved to be accurate, AFTER freeing up significant quantities of transaction log drive space.

Thanks to all who so swiftly applied their expertise in answer to this (never seen before by me) problem.


"CREATE INDEX" Background process in SQL Server While analyzing our environment, I've noticed a recurrent blocking that usually happens around the same time, our monitoring tool says the blocked process is a "CREATE INDEX", however, the index maintenance is not in-progress when the issue arises. Looking at the details, the command seems to be running in the background.

Has anyone seen this background task before? I'd like to understand how/why this happens. I've looked it up and could not find much information.

I don't have automatic tuning enabled, also this is a small data mart, the ETL process doesn't include any index create statement. When I look at the blocking history, I can't really see what was the complete statement, it doesn't show plan handle or anything. I'm guessing it's an internal process as the SPID is below 50.

There is a similar thread here:
https://social.technet.microsoft.com/Forums/windowsserver/en-US/65283481-f4e7-422f-99bc-2677699ba70b/background-process-create-index?forum=sqldatabaseengine

If you are seeing the same index(es) being created over and over again, this is most likely being initiated by the application.  Some applications, particularly reporting or analysis applications, will drop indexes before importing data, and then create them again after the import is complete.

Another possibility is that Automatic index management is enabled.  However, you won't see this creating the same index over and over again, unless something is dropping an index that it creates.

Can you find that index--what table is it on?  I'm wondering now if it might be on a tempdb table that is created, populated, indexed, and dropped repeatedly.

If you can catch it happening with sp_whoisactive that should provide more clues.  Set @show_system_spids = 1.


Query to match exact column or partial data of the column: SQL Server / Azure Bit confused on how to get it work better than what has been done now. I am new, apologies if I'm asking for too much.

I have trip details which have from and to locations stored in database from Google MAPS API.

One such record could be like this :

  From : Bangalore, Karnataka, India To : New Jersey, United States

If someone search for

From  : Bangalore, Karnataka, India

or

From : Karnataka, India

or

From : Bangalore

or

From : Karnataka

or

From : India

or

From : Bangalore, India

Query should be success and same condition applies to TO column also. And if any one of FROM or TO column match is valid result.

How to achieve above solution with ordering better matches on top?

What I tried

Exact match solution.(Either matching FROM or matching TO and MATCHING BOTH, if given) Not a right solution as it doesn't give all matches.
Using LIKE but results are in right order (most or complete matches should be on top is not achieved by me)
FREETEXT and CONTAINS cannot be done as my SQL Server is on SQL Azure which doesn't support.
Create 3 columns for FROM and 3 columns for TO and populate data from FROM to from1,from2,from3 and query for

(exact match)
UNION
this with (query for keyword LIKE in from1,from2,from3 columns)

and do the same for TO column.

None of the solutions as I did now are convincing for me. Any better possible solutions?

As @MarkSinkinson suggested you probably want to bite the bullet and move to the geography datatype.  Sometimes a little short-term pain will save you a lifetime of misery.

Options 1 and 3 clearly can't work for the reasons you mentioned.
Option 2 doesn't work either unless you want a search for India to return results for Indiana too.
Option 4 shows the most promise assuming from1, from2, from3 -> city, region, country, and your input search terms follow the same pattern (city, region, country).  If they must be a random list search terms then you may run into problems assigning weights for locations like Lima (city), Lima (region/province), Peru (country) because Lima matches two categories.

As I suggested option 4 will probably be the most fruitful, given the caveats I listed for option 4 and the recommendation to abandon text matches in favor of the geography type. The following query isn't sexy but it should be close to your original requirements.

declare @city nvarchar(50) = N'Bangalore', @region nvarchar(50) = N'Karnataka', @country nvarchar(50) = N'India';

;with [City_CTE] AS
(
    select
        <table PK> -- Unique match guaranteed
        , 1 as [present] -- Convenient summing later via the isnull function
    from <tablename>
    where
        [city] = @city
)
,  [Region_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [region] = @region
)
, [Country_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [Country] = @country
)
select
    *  -- Select the columns you need
    , isnull([ci].[present], 0) + isnull([re].[present], 0) + isnull([co].[present], 0) as [MatchWeight]
from <tablename> [t]
    left join [City_CTE] [ci] on [ci].<table PK> = [t].<table PK>
    left join [Region_CTE] [re] on [re].<table PK> = [t].<table PK>
    left join [Country_CTE] [co] on [co].<table PK> = [t].<table PK>
order by
    [MatchWeight] desc


Postgresql text column character Limit Im not even close to a DBA, so plain English please. I recently needed to look into a performance issue on a relatively small PG database, but yet is performing very slow. the server is running PGSql 9.0.22. Its largest table is about 30Mb, and the entire db is hardly 80Mb. However, i found that one of the tables has a text column (which i assume has no width limit) which probably stretches about 10 miles long. I copied the text from one column into Word, an i got 377 pages and a word count of 69814 (as in sixty eight thousand). Again, im not im not expert, but it just doesnt seem right to me. Can PG handle such large text columns? Is this considered a normal design/practice?

EDIT

Thank you both for your response. I would have to research its purpose and types of queries running against it. For the sack of argument, lets assume the column has a list of fruits, and is just a point of reference. Meaning, there's a query which just returns a True or Flase if Avocado is on that list, and then moves on to another action based on the True/Flase results. So theres a text "Fruits" column in the "Foods" table, the text in the column might look like this (with just spaces between each fruit):

Apple Avacado Mango Orange Cherry Grape Peach Srawberry ... and on listing about 68,000 fruits

Would it have been beneficial, or perhaps considered a better design, if each fruit was in its own field. In other words, have 68000 fields under the "Fruits" column, as opposed to one text column with 68000 words?

EDIT
I didn't mean a column for each fruit. I meant a row for each fruit under the fruit column

**Columns - Fruits, Vegetables**

  Rows      -Apple     -Corn
            -Avocado   -Tomatoes
            -Mango
            -Orange
            -Cherry
            -Grape
            -Peach
            -Strawberry

And so on...a list of about 1.5 million

EDIT

In short, I mean having each piece of data in its own field as opposed to using a large text column. So for example, if this was a spreadsheet, the cell A:1 would be the column (header) and A:2, A:3, A:4 the rows (each holding a fruit name). Not all fruits in A:2.

You are right in thinking that items of a list should be stored as individual values of a column, i.e. in separate rows of that column, rather than as a single value (CSV string or anything like that) – at least if you expect to have queries against individual items of the list. Relational databases are designed to work that way, and storing multiple values of a column as a list (single value) is a no-no in such cases.

Regarding your data sample of fruits and vegetables, I am assuming that you are not thinking of splitting two lists of values exactly like that, storing each list's elements alongside each other on the same row without there actually being a relationship between them, just for the sake of normalisation of the way the vegetables and fruits are stored. That would be wrong.

I mean, if currently you have a row with a list of fruits and a list of vegetables such that each fruit and each vegetable are related to this particular row, i.e. like this:

FoodsID  Fruits                      Vegetables     other  columns
-------  --------------------------  -------------  -----  -------
100      Apple,Avocado,Mango,Orange  Corn,Tomatoes  ...    ...

and you want to expand each list into a row set while keeping the relationship of each fruit and each vegetable to the Foods row, you do not just add more rows and put the first item of one list together with the first item of the other list on the same row, then the second item of each list on another row and so on, like this:

FoodsID  Fruits   Vegetables  ...
-------  -------  ----------  -----
100      Apple    Corn        ...
100      Avocado  Tomatoes    ...
100      Mango                ...
100      Orange               ...

Again, that would be wrong. Fruit Apple may well be related to food item 100 as may vegetable Corn, but if these two items are not related to each other, there is no reason to store them on the same row.

Commonly, when you want to store a relationship between a subset and an item, you use a separate table. As you have two subsets of different kinds that are related to the same item, you just use two tables. So it would be:

table Foods:

FoodsID  other  columns
-------  -----  -------
100      ...    ...

table Fruits:

FoodsID  Fruit
-------  -------
100      Apple
100      Avocado
100      Mango
100      Orange

table Vegetables:

FoodsID  Vegetable
-------  ---------
100      Corn
100      Tomatoes

where FoodsID in table Foods would be the primary key of that table and same-named column in tables Fruits and Vegetable would serve as a reference (foreign key) to the corresponding row in Foods.

This way, when querying against individual items of either list as belonging to that particular row in Foods, you would probably use a join. Your typical filter conditions would be as simple as Vegetable = 'some vegetable' or Fruit IN ('some fruit', 'some other fruit') – not FindPos(Vegetable, 'some vegetable') > 0 or anything of the kind. The conditions would be simpler because each column would now contain one value per row. That will lend well to further optimisation of performance, because when you query often against a column value (without applying a function to it first, that is), you can make such queries faster by adding an index on the column – something that would be pointless for lists stored as a single value.

More can be said on this topic, which would, however, be beyond the scope of your question. I suggest you look up the terms I highlighted in bold for more information.


MySQL / PostgreSQL: how to call a script when a row inserted / updated Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).

Every second multiple rows are being inserted or updated on the first DB. For each inserted/updated row I want to call a Python script, which connects to another DB on another host, makes some queries there; if everything is fine the script writes these rows (which have been inserted or updated) to the second DB.

After some research I see the following options to call the script:

Use triggers and do everything in SQL (it looks like the connection to another DB should be not a problem for PostgreSQL, but may be a problem for MySQL if not using federated storage engine)
Use UDF (user-defined functions)

I also read a bit about different patterns of architecting databases, like Saga or publish/subscribe, but didn't find any concrete practical examples of how to do this.

Two of my questions are:

Are there any other possibilities to implement the wanted solution?
The two options I mentioned (triggers and UDF): do they have any hidden traps? Which option is better in terms of performance?

In Postgres, I would do this as a trigger that uses a foreign table to make the remote table appear as a local one.

The trigger function can then query, update or join the remote table as if it was a local one and Postgres will be smart enough to push the work to the remote server (e.g. filtering, grouping, sorting etc).

The table can also be updated directly from within the trigger, no need for some scripts outside the database

If you prefer Python instead of PL/pgSQL you can write the trigger function in Python.


What are the differences between Oracle UCM and Oracle Content Server? Whenever I do searches on them, I keep seeing them in search results together.

What are the differences between Oracle UCM and Oracle Content Server?
Are they just two names for the same thing?
Is one a subset of the other?

found partial answer to this . one is a subset of the other .

" Oracle UCM is a suite of enterprise content management products that include Oracle Content Server and Oracle Site Studio. "

http://download.oracle.com/docs/cd/E13155_01/wlp/docs103/ucm_adapter/intro.html

still not sure the differences .


In SQL Server 2016 what is sys.tables is_external field? In SQL Server 2016 the sys.tables appears to have a new field called is_external.  But what does is_external actually mean and what is it used for?

MSDN says the following, which is not very helpful:

  Indicates table is an external table.
  0 = The table is not an external table.
  1 = The table is an external table.

This is my interpretation ..

SQL Server 2016 allows you to configure POLYBASE - accesses and combines both non-relational and relational data - from SQL Server.

It allows you to run queries on external data in Hadoop or Azure blob storage.

SQL server has a column is_external to identify that the table is external. You can find more on external tables using sys.external_tables catalogue view.


VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database? When using SSIS with Oracle database, it seems to always assume that a VARCHAR2 maps to a DT_STR.  However, if my Oracle encoding is set to AL32UTF8, I should be able to store any unicode character in a VARCHAR2, right?  Is there any way to force SSIS to map an Oracle VARCHAR2 to a DT_WSTR? Should there be?

I looked up a few other questions on this, but nothing seems to address it.

I have a data source that has many columns as DT_WSTR, and I want each to go into a VARCHAR2 column without having to convert every column.

Looks like I need to be careful to specify my column length in CHAR. For example, Instead of VARCHAR2(255), I need to ensure it is VARCHAR2(255 CHAR).  If I do this, SSIS sees it as DT_WSTR.


How to restore MySQL databases from database files? Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f and there was an error about mysql-server-5.6 being corrupted.

So after a lot of trail and error I was able to remove mysql-server-5.6 following commands here : https://askubuntu.com/questions/172514/how-do-i-uninstall-mysqli

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

Afterwards, I reinstalled mysql server by:

sudo apt-get install mysql-client mysql-server

Then when I tried mysql -u root -p  and inside mysql, I couldn't see any previous databases..

I now realized that I've removed the /etc/mysql/my.cnf file from the system and my understanding is that mysql doesn't know where the databases files are and they haven't been loaded into the databases yet.

I should also mention that, the /etc/mysql/my.cnf file is basically empty with these two lines:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I have .sql files for each database from a previous logical backup in February. The issue is that I don't know if there were any updates between the February backup and now (as I'm new to manage this database). I also have the path to a directory (let's call it a B directory) that contains what I believe the database files as many directories names match the database names. And in this B directory, many directories seem to be modified after February so it's very likely there were updates.

I'm reading other questions and the B directory contains the following files among others:

ibdata1
ib_logfile0
ib_logfile1

tokudb.directory
tokudb.environment
a bunch of *.tokudb

I understand I could use the following command to restore the February backup.

mysql -u username -p database_name < file.sql

But I'm wondering if there is any possibility to bring those updated database files back to life to MySQL. If so, how to do this? I'm guessing I need to somehow restore the previous /etc/mysql/my.cnf file.. but not sure what are required lines to put from this list I found: http://www.fromdual.com/mysql-configuration-file-sample

I'm not very familiar with MySQL backup and I'm still reading about mysqldump and what it does. I'm wondering if I can use the February .sql files and somehow apply it to the latest mysql database files??? Hopefully this and the title are clear..

Thanks so much for the help!

This is what I do to start the mysql daemon manually.

I've also included my own my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally.

Start the daemon like this:

  ./bin/mysqld --defaults-file=./my.cnf

And start the client with:

  ./bin/mysql -S ./mysql.sock -u root -pdba

Both mysqld and mysql client are launched from the basedir. This can be scripted easily of course.

This is my my.cnf - it's a bit of a mess, but hopefully it should help. I stress that this is not a production server!

[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.pid
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64
datadir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/logfile.txt

#log_error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
log-error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log

slow_query_log_file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.sock
host            = 127.0.0.1
user        = pol
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1 -- ignore this, it's a setting for abrowse, an external programme.

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log        = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#pid-file   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#log-error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log


Is there a way to improve performance here using No Exists? I have three tables. One table has information on users, such as their name, bio, age etc. The primary key is the Id field alone. Second table shows people users follow (user_following) and the third table is to store relations with people who follow me (user_followers).

I want to find information on those users who have been removed from both the tables.

Users can have large IDs and hence the ID column is varchar with the lenght being max 25 characters as of now.

My query is this:

select *
FROM followers_info finfo
LEFT JOIN user_followers uf
    ON uf.follower_id = finfo.follower_id
LEFT JOIN user_following fing
    ON fing.follower_id = finfo.follower_id
WHERE uf.follower_id IS NULL
  AND fing.follower_id IS NULL

Attached is the explain results of this query:

Is there a way to improve the performance here?

You can try to rewrite it as:

select *
FROM followers_info finfo
WHERE NOT EXISTS (
    SELECT 1 FROM user_followers uf
    WHERE uf.follower_id = finfo.follower_id
)
AND NOT EXISTS (
    SELECT 1 FROM user_following fing
    WHERE fing.follower_id = finfo.follower_id
);

or:

select *
FROM followers_info finfo
WHERE NOT EXISTS (
    SELECT 1 FROM user_followers uf
    WHERE uf.follower_id = finfo.follower_id
    UNION ALL
    SELECT 1 FROM user_following fing
    WHERE fing.follower_id = finfo.follower_id
);

Generally no better nor worse, but worth a try


SQL Server schema intact but every record from every table is gone We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real maintenance we've done on it is to move the partition the database files resided (done nearly a week ago) and we ran the Database Engine Tuning Advisor to look at some suggestions for new indices yesterday around 12PM.

Just after 5PM while the software was being used as usual someone notified me that the program no longer seemed to work properly. I checked the database and not only was data specific to his use of the program was gone, but every table of fifty tables or so seemed to be wiped of all records.

After talking to the application developers in extensive length, it became clear that this wasn't a remotely possible capability of their software.

In the scramble to get back from our feet we cloned a backup of the VM over the running copy between restarting and it's been running five for about a day ever since. Consequently, we've no logs of the events leading up to the problem itself.

Have you seen anything like this happen before? (All records in all tables of a database just... gone.) Does anyone have a reasonable theory as to how this could occur and what if anything would be a reasonable measure of prevention?

This happened at my company.

Someone re-ran the schema creation scripts from within the application's admin interface--something like an "initialize database" command. These scripts included a series of IF EXISTS()...DROP...CREATE statements to drop and recreate each table and constraint. Since they were designed to wipe a database even if it was already populated, they were carefully structured to account for foreign key relationships. Thus, the database schema was there and looked great, but all of the data was gone.

To prevent this, revoke the appropriate DDL permissions from the app's database login after it has created its own schema, or make sure any "initialize database" functionality your application has includes several safety checks and presents appropriate warning to the user regarding what's about to happen.


Changing the grid listener port of an Oracle Database Appliance X8-2M I need to add an extra Port: 1522
Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
Services Summary...
Service "xxxx" has 1 instance(s).

/u01/app/19.0.0.0/grid/network/admin/listener.ora->
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                #

Every time I edit the listener.ora The listener fails to start:
I tried:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1522))
    )
  )
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

srvctl start listener
PRCR-1079 : Failed to start resource ora.LISTENER.lsnr
CRS-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/xxxx/crs/trace/crsd_oraagent_grid.trc"
Listener failed to start. See the error message(s) above...
2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] (:CLSN00010:)Utils:execCmd scls_process_join() uret 1
2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] clsnUtils::error Exception type=2 string=
CRS-5016: Process "/u01/app/19.0.0.0/grid/bin/lsnrctl" spawned by agent "ORAAGENT" for action "start" failed: details at "(:CLSN00010:)" in "/u01/app/grid/diag/crs/etabonxrdbs0010/crs/trace/crsd_oraagent_grid.trc"
2021-02-17 15:32:19.463 :    AGFW:2610939648: [     INFO] {1:35727:65428} Agent sending reply for: RESOURCE_START[ora.LISTENER.lsnr etabonxrdbs0010 1] ID 4098:1355034
2021-02-17 15:32:19.463 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] LsnrAgent::Lsnrctl::start lsnrctl start complete lsnrname:LISTENER output:
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2021 15:32:19
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
...
2021-02-17 18:19:34.200 :CLSDYNAM:2606737152: [ora.LISTENER.lsnr]{1:35727:1260} [check] clsnUtils::error Exception type=2 string=
CRS-5020: Not all endpoints are registered for listener LISTENER
I tried: srvctl modify listener -p "TCP:1521/TCP:1522";

srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521, 1522
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

The command is definitely (user grid):
srvctl modify listener listener -p "TCP:1522,4444,nnnn"

Our issue was that the port we wanted to use, was used by another service on the ODA (e.g. ONS). The command completes but the listener ignores the port than. This explains the log above maybe.
We also needed to enter the local_listener on the ODA CDBs/ASM:
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1522))';

We opened a SR at Oracle support and got a feedback:

Right now the dcs agent on ODA does not handle multiple listener port.
We can configure listener to use multiple ports on ODA. But we face
issues during create-database, register-database. Enhancement has been
logged for such issues which will be fixed in future release Enh
30095060 - ODACLI CANNOT REGISTER DATABASE At present I would not
recommend to add multiple ports to listener.


make exist table as inherit i try to create parent table for exists table, by next algorithm:

-- CREATE TABLE
CREATE TABLE t1 (id serial PRIMARY KEY);
CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id));
-- insert data
INSERT INTO t1
SELECT * FROM generate_series(1,4);
INSERT INTO foreign_t1
SELECT generate_series, generate_series % 3 + 1
FROM generate_series(1,9);
-- CRETAE PARENT table
CREATE TABLE parent_t1 (LIKE t1 including all);
-- ALTER t1 DO it inherit of the parent
ALTER TABLE t1 INHERIT parent_t1;
-- TRY TO MOVE FK TO the parent
ALTER TABLE foreign_t1 DROP CONSTRAINT foreign_t1_t1_id_fkey;
ALTER TABLE foreign_t1 ADD CONSTRAINT
foreign_t1_t1_id_fkey FOREIGN KEY(t1_id)
REFERENCES parent_t1 (id);

But cath the error:

ERROR:  insert or update on table "foreign_t1" violates foreign key constraint "foreign_t1_t1_id_fkey"
DETAIL:  Key (t1_id)=(2) is not present in table "parent_t1".

And when i try to validate the data looks like all exists:

dev=# SELECT id FROM parent_t1 WHERE id = 2;
 id
----
  2

i can't understand what i missing?

From the docs:

  A serious limitation of the inheritance feature is that....foreign key constraints only apply
  to single tables, not to their inheritance children.


attach database - verify - detach from a script or a batch file for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most 2005.

I need to verify the integrity of each and follow up on the ones that fail.

after all that .... My question is:

Is it possible to attach - verify - detach each database using a batch file or a simple script?

I've used sqlcmd for similar jobs, but with this volume I thought I'd see if there was a simpler way.

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)
}

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
      $ret
    }
} catch [system.exception] {
  $_.Exception.ToString()
}


String comparison with integer is not working sometimes in MySQL 5.5 I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working.

Code is "value = 1", here 'value' is declared as varchar(45) and its value can be anything either 1 or some string.

But after upgrading, it is not working properly. Sometimes it works fine and sometimes not. I tested in different setups, in some setups it is working but in some other it is failing.

Here if I change the code as value = '-1', then it is working fine in all cases and different setups.

But I had used this case in my application in many places.

Is there anything to be handled for newer version other than using single quotes?

Please help me out of this.

This isn't a full answer but you might try looking over the 5.5 change list starting at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html

Note the section nav on the right that goes subversion by subversion.  Skim through each looking at "incompatible changes".  If you find something describing what you are seeing it will at least give you better context of what is happening and why.


Building A SQL table horizontally VS vertically What is a better design principle when designing SQL Server tables.

One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows)

I have a situation when there are normally a number of items, say 2- 12 that are generally related to one another.
Just to keep it simple, they would have the same values in 4 of their columns but 3 would be different.

Would it be better to now add the 12 entries in their own row, or would it be better to say add additional columns that would help me combine all the entries into one row, but be able to identify which one was which.

I dont think I have explained the situation well enough to get a specific answer relating to my situation.

But I am asking in general  which is better practice?

The extreme example of the vertical design pattern is called entity-attribute-value. Generally speaking, you don't want to use the EAV design pattern with SQL Server due to challenges with indexing and constant joins.

To find the right middle ground, review Joe Celko's post on normalization in SQL Server Central's Stairways series. Note the sections on 3rd normal form.


Need help designing an Access Control List (ACL) database structure I need help with a database design for an ACL (Access Control List) system.

Business rules

Let me explain the requirements:

Any business object in the system will get an ACL that explicitelly defines who can access the object.
Our system has groups of users and the ACL will specify which groups can access the object.
Any user can be member in N groups.
The user will not even see the objects if she is not included in the ACL.

Current considerations

There are tables that can hold hundred thousands of these business objects and thus I need a design that can quickly select the "visible" rows for them.

My idea is to add an ACL_ID to the tables with the business objects. Let's say I get a table for Orders and I need to control the accesibility:

CREATE TABLE Orders
(
    ID  int not null,

    ... lots of columns with relevant information

    ACL_ID  int not null
);

I add an ACL_ID column constrained as a foreign key into an AccessControlList table:

CREATE TABLE AccessControlList
(
    ACL_ID int not null,

    ... any needed information
);

Another table could hold the user groups that are included in the list:

CREATE TABLE AccessControlListItem
(
    ACL_ID int not null,
    USER_GROUP_ID not null
);

The AccessControlList defines which groups of users can access the elements. If I had 100 different groups I could define that users from 3 groups can access the row.

As there are potentially millions of business objects I thought that probably would be better to just create AccessControlLists rows for the different combinations. For example, if the user groups "A", "B" and "C" are included in a list with ID = 125 I could reuse this ACL for any business object with the same combination. If someone adds a group "D" to the ACL I would need to create a new list with a new ID. The ACLs would be then "lazy-created": everytime I need a new combination I create the new list. Of course I will need a "garbage collector" to eliminate lists that are not used anymore or I leave them as they could be created later on.

Ok, now I reduced the number of elements in the AccessControlList table from millions of objects to maybe some thousands. In any case I believe that the design will be very expensive for queries. A query for orders without any other filter than the ACL could be something like this:

select <columns> from Orders where ACL_ID IN (
      select ACL_ID from AccessControlList where USER_GROUP_ID IN (
           <select the groups for the current user>)
)

The user could potentially be a member in 100 different ACLs.

The question

Any experiences with a similar design? Any comments about some other way of implementing it? I would be very grateful for any ideas or comments. Thank you very much.

I favor computing and materializing granular object access entitlements so they are simple and cheap for the database to enforce at runtime.  And to simplify the relational model.

Order(OrderId, ...)
Order_AccessControl(OrderId, GroupID, AccessType)

Every time you create a group or an order you would generate the AccessControl entries using (potentially complex) business logic and configuration data, but storing, auditing and enforcing the security is simple.


Selecting attributes from multiple tables via foreign key Apologies if this question has already been answered. Couldn't find the answer myself.
I'm trying to work out the SQL to extract data from one table which is being referenced from another table. My explanation might be terrible due to looking at it way too long, so see my tables below:

Table 1
Table 2

(PK) ID (int)
(PK) ID (int)

Att_1 (str)
(FK) Att_1 (int)

Att_2 (str)
(FK) Att_2 (int)

Att_3  (str)
Att_3 (str)

Essentially I'm trying to select all from table 2, but table2.att_1 and table2.att_2 are foreign keys of table1.id. What I want to achieve is extract the following:
Value of Table2.ID
Table1.Att_1 where Table2.Att_1 = Table1.ID
Table1.Att_1 where Table2.Att_2 = Table1.ID
Value of Table2.Att_3
The final SQL will be used in a PHP script, so if its not possible to do this in a single line of SQL, I can split the requests up one by one. Just looking for some good old CPD.

Essentially I'm trying to select all from table 2, ...

So we start with Table2 as our basis for the query:
FROM Table2

... but table2.att_1 and table2.att_2 are foreign keys of table1.id.

We'll likely need two (outer) joins to the same table (Table1), one for each foreign key:
FROM Table2
    LEFT JOIN Table1 ..
    LEFT JOIN Table1 ..

What I want to achieve is extract the following:
Value of Table2.ID

SELECT Table2.ID

Table1.Att_1 where Table2.Att_1 = Table1.ID
Table1.Att_1 where Table2.Att_2 = Table1.ID

Right, those verifies that we want 2 left joins and the ON conditions:
SELECT
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_1 = Table1.ID

and
SELECT
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_2 = Table1.ID

Oops, we have a problem to combine the two joins. We'll need to alias those tables as we have a table appearing twice and it's good practice nonetheless:
FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID

Value of Table2.Att_3

SELECT t2.Att_3

The whole query becomes (after adding some column aliases as well):
SELECT t2  . ID     AS  ID,
       t1a . Att_1  AS  Att_1_a,
       t1b . Att_1  AS  Att_1_b,
       t2  . Att_3  AS  Att_3
FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID
  ;