texts: Multi-Master Replication / Mysql Slave Replication from Multiple Masters with Same Schema Names

texts:
Requirement::
Between two datacenters (DC, DRC) databases should be seamlessly replicated withstanding the datacenter failures. Both the data centers will be having on-premise servers.
DC - Primary Datacenter, DRC - Disaster recovery center. Both the data centers will be up all the time. And the traffic will be routed to 70% to DC and 30% to DRC.
Currently, we are using Mysql 5.7 community version and our application is well tested with it.
The application will have on average of > 500-1000 inserts/updates per second and continuous read operations on all tables (>100). Could be more as well. Took below stats from Innodb status of our database from other location. But not sure how trustworthy these are
ROW OPERATIONS
0 queries inside InnoDB, 0 queries in queue
36 read views open inside InnoDB
Process ID=2417, Main thread ID=139811035186944, state: sleeping
Number of rows inserted 19946591703, updated 265822234, deleted 140784124, read 8314412966825
413.81 inserts/s, 98.84 updates/s, 0.69 deletes/s, 3024258.44 reads/s
Background::
At first, we thought of making master-master replication by keeping only one DC Database as the primary and the DRC as the standby by replicating in real-time (Both the datacenters will be connected using a Private link with good speed). this is because we thought to avoid Sequence issues when the connection is down between the datacenters. So DC App servers and DRC App servers will be connected to DC Database itself. When the DC Database goes down DRC Database will act as primary and vice-versa.
But the challenge is when the private link between the centers is down. The DC will be up and running but DRC will face downtime due to the unavailability of DC Database. Here we can make both DC-DRC acts as primary in their respective centers to avoid downtime, But if we done this there is a chance of having duplicate primary keys/replication issues when both the centers are up again and need to be in sync.
So currently we are considering the below approaches
Approach 1::
Enable Master-Master replication and keep only 1 master in Live by connecting both the center's app servers to a single database. if the private link between both the centers is down. enable the replication over the internet by allowing connections to particular ip's at the firewall level.
But what if the internet also goes down but the data center is up and running? :), we are stuck here for now.
Approach 2::
Maintain a separate sequence(CASE-ID - Auto increment) for all tables in both the databases DC-DRC and replicate all that data into a common database by following a common sequence for each table. Can this be done by following any MYSQL official/proven way?
In worst-case scenario, we are thinking to use an ETL Tool talend to sync the two databases data into a single database by following a common sequence for each table.
Approach 3::
Maintain different schema names/different sequences (CASE-ID - Auto increment) for both the datacenters and then sync both the database data into a single database using channel replication(Multi-source).
Sorry for the long post. I just want to explain the full scenario to get the best possible answer :).
Please suggest if anyone has a better approach to fulfilling the requirement, I am still a beginner in Database setup so please ignore if I said anything wrong above :). I am browsing all over the internet to find a similar scenario but couldn't able to find it and we have very little time to go-live with production. So any solution/ suggestions are much appreciated.
Thank you!


texts: importing database backup to phpmyadmin return #1064

texts:
When I tried importing mysql database backup to MySQL using phpMyAdmin
I got the following error message:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'PK00PK' at line 1

In another post, it says open the sql document and delete weird lines and shall work
where is the sql document that i should open?
i m not a specialist in this.

  PK00PK�"�8B_�5=db.optKIMK,�)�M�H,JL.I-�-N-�--I��J�I���$�d�灅��S�R�s��3�PK�"�8:�h�n�"f2blog_attachments.frm��?k�@�'���K��K����Ԃ
  *-u(X� 5��bA���E�C�� �^���[|C��Cxɽ�ݷƙ�h��ׄZd�0���f���w�O��A�4@!�B!���L���_�+P'{h[�i=o����p�ċ[_��\p/|]��[��W���[1lɝM0��i�.�;��e�]j,�M]U��Ю�"�Yg�/�SM禉q)p<�7����瘖)���(�̧!�Q�|�0YL@�/��K�PK%�8RDE�DPf2blog_attachments.MYDc``���͍��-,��
  ҅ܟ��x�{��}˞m�
  ���MLO�/�*HM�Z�°���a%��K�PK$�8��Y��f2blog_attachments.MYI�������q���L@�����P#
  <0F̀�^�$�0�r����0\���>K(��� ���@��l��B -�\
  ��ؘA&%0,bdd�B{���JF�.LY�OX�(F=u��hL%X�Q0
  �P��(������d�h�2
  F�H��PK�"�8�^��y�"f2blog_categories.frm�ؽJ�P���G�{S�����c�P_����Rup,M�`M�����(�ѡ��E�|
  �'M�v'<�������ܡpϧP��p��M9�    ���

The database was created in linux and I'm trying to import it to MySQL using phpMyAdmin running in Win 7


texts: Multiple Replication Type Publications on Single DB

texts:
I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as they are not allowed by the vendor DB1)

When considering replication options for a reporting server, I was limited with DB2 as a number of the custom tables which have been added over time do not have Primary Keys, so transactional was out, leaving really only Snapshot as an option. DB 1 is working well with transactional.

Having now run this set-up for a while, with the Snapshot of DB2 (around 1GB) running every 20 mins, I am getting some performance implications specifically around blocking when the snapshot is generating.

Ideally I would like some of the key tables to run transactionally, whilst a single snapshot takes care of the remainder of the DB out-of-hours, but cannot find much guidance on this anywhere.

My real question is, is it viable to run 2 types of replication on the same database as long as the articles do not cross-over?

Many Thanks in advance!


texts:
Possible Duplicate:
  Database Design: New Table versus New Columns

I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important to the question). Each tuple in the blocks table can have a lot of metadata associated with it. Based on initial thought, the relation might look like:

block
-----------
bid : integer PK
name : varchar
prop1 : numeric
prop2 : numeric
...
propN : numeric

Correct me if wrong, but I would think this is normalized because every attribute is solely dependent on the PK.

However, I'm wondering if there are performance benefits to separating the "properties" into a separate relation such as:

block
-----------
bid : integer PK
name : varchar

block_metadata
-----------
bid : integer FK (references block(bid))
prop1 : numeric
prop2 : numeric
...
propN : numeric

One immediate benefit is that I can save space when I have a block that doesn't have any block_metadata (which is possible in my application). However, the downside is that a join is needed to access this information.

Are there any other benefits/trade-offs? Any details about how a RDBMS manages wide (30-50 column) tables? How does width affect performance? I'm using Postgres, FYI.

Any input or external references appreciated!


texts: Continuing failovers on production server

texts:
At least a few times a day, my main production server has recently begun failing over.  Only, the quorum doesn't move to the other node.

I ran the validation report, and got a lot of info I don't understand.  It is an HP ProLiant BL680c G5.  Some of the info in the report that I'm wondering about:

Degraded:
HpCISSs
HP MPIO DSM for EVA4x00/6x00/8x00 family of Disk Arrays
Link-Layer Topology Discovery Mapper I/O Driver
Mount Point Manager

Those are just a few.  I'm not so worried about "errors", but "degraded" seems to imply that it should be running but isn't doing so well.

System details:
sql - 10.50.4000.0
Windows - Windows NT - 64 Bit

It really is going to be awful if it IS an MPIO issue.  That has happened many times, and the DBA team has been accused of modifying the settings!  I'm the team lead, and even I have no idea what half of this means (I know what MPIO is, and I recognize all the SAN stuff, but troubleshooting? Nah.)

Interesting new information - right before this began happening, we updated the Firewall Service Module, and rebooted the core switches.

I'm thinking there are settings which don't have the correct defaults?

Logged during the failover:

  Cluster Agent: The cluster resource FileServer-(server)(Cluster Disk 1) has failed. [SNMP TRAP: 15006 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource SQL Server Agent has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource SQL Server has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource Analysis Services has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource FileServer-(servername)(Cluster Disk 4) has failed. [SNMP TRAP: 15006 in CPQCLUS.MIB]"

it is really odd, because there aren't a lot of error messages. the only real info I have is from the validation report. Disks 1-4 always fail, but not logged in the same order, and then the quorum just stays on the node it is on.

After speaking with the network guy, he thinks that perhaps when the core switch was rebooted, it affected the preferred routes for the NICs.  He's going to clean things up a bit this weekend (removing the file shares and recreating them) and we'll see where we are. The adding/removing didn't work.  It failed over yesterday evening.

We are using fiber channel to connect between the servers and the storage array.  We just patched Windows (using Shavlik) and now we have the same issue.  I'm starting to wonder if it is some default setting in MPIO that keeps returning.


texts:
I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission
How can I REMOVE that permission? Is there a stored procedure like sp_addsrvrolemember that works on the contrary and that I can use to drop that permission for the same user for which I granted it?
I tried using sp_droprolemember, but i got a "dbcreator is not a role" error message.
I need to do it programmatically.
Many thanks in advance!
(Sorry if a similar question was already asked, but I was not able to find it)


texts:
I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this approach to improve performance and it worked perfectly for a long time. However, recently this approach has started to take too much time.

So, instead of using the above, I execute index rebuild. It completed with one-six of the time taken in the first approach.

Why did the first approach become slow over time, and why is the full index rebuild better?

EXECUTE dbo.IndexOptimize @Databases = 'MyDBname'
    , @FragmentationLow = NULL
    , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationLevel1 = 5
    , @FragmentationLevel2 = 30
    , @UpdateStatistics = 'ALL'
    , @OnlyModifiedStatistics = 'Y'


texts: SSIS transfer SQL server objects task -> Foreign key constraint error

texts:
I try to copy a database from 1 server to the other to build a staging environment. I do this with SSID. First I delete the existing database on my staging server, create a new and empty one and then I run the script. I copy everything. Most of my databases copy correctly except for 1 which gives me a Foreign Key constraint error. What can I do to fix this? I tried googling, but without any results.

   SSIS package "XXXXXXXX.dtsx" starting.
Information: 0x4002F418 at MyDatabase, Transfer SQL Server Objects Task: There are no Logins to transfer.
Error: 0xC002F325 at myDB, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "ALTER TABLE [dbo].[TemplatePropertyValue]  WITH CH..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_VALUE1_Template". The conflict occurred in database "myDB", table "dbo.Template", column 'Id'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".
Task failed: myDB
SSIS package "XXXXXXXXX.dtsx" finished: Success.


texts: Optimal Number of MySQL InnoDB Buffer Pool Instances

texts:
Server Characteristics

Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL)
Number of CPU Cores: 6
I have data in the db amounting to about 2GB
I have InnoDB Buffer Pool Size set to 4GB

Which is better:

Innodb Buffer Pool Instances set to 1?
Innodb Buffer Pool Instances set to 2 (2GB in each)?
Innodb Buffer Pool Instances set to 4 (1GB in each)?
Innodb Buffer Pool Instances set to 8 (the default setting)

I'm thus not sure as to how to reason when it comes to Buffer Pool Instances and also the whole "use instances or suffer OS Swap when having such large InnoDB Buffer Pool Size".


texts:
In a large database, an activity was performed to upload the data.
Due to some mistake, a month's data was entered again, which was left in the temp holding table.
So, to cover this up, we took dump of 2 tables from the back-up restored on another machine.
Renamed the tables where error occurred and restored from the backup.
But when the tables were restored the constraints failed to load as the same name is already in use.
Used the oracle exp/imp commands to export and import the data.

I'm not a DBA, but need help on this issue.


texts: SQL Error(1054): Unknown column in 'where clause' when trying to update. Column is actually available

texts:
I have two tables namely table_a and table_b.
Table_a has several columns including student_id and course_start_date
Table_b has several columns including student_id and fee_pay_date
student_id is INT field and course_start_date & fee_pay_date are DATE fields
I want to update column course_start_date in table_a by reading fee_pay_date from table_b for each student.
When I write the following command in MariadB, it throws up an error:
UPDATE table_a
SET table_a.course_start_date = table_b.fee_pay_date
WHERE (table_a.student_id = table_b.student_id);

Error: SQL Error(1054): Unknown column 'table_b.student_id in 'where clause'

I have checked the tables. All column names are correct. Can anyone help on how to resolve this issue?
Thanks in advance.


texts: Query to fine "Order_num" that has no two equal "product_name"

texts:
I have a table with below structure:
create table A_TEST
(
  order_num      int,
  product_name   varchar(50),
  product_amount int,
  product_price  int
);

Sample data :
insert into a_test (order_num , product_name , product_amount , product_price)
values (1,'APPLE',1000,2000),
       (1,'APPLE',2000,5000),
       (1,'APPLE',4000,3000),
       (2,'ORANGE',280,3000),
       (2,'APPLE',200,3000),
       (3,'BANANA',2000,3000),
       (3,'APPLE',200,3000),
       (4,'POTATO',123,3000),
       (4,'POTATO',2000,3000),
       (4,'APPLE',2000,3000),
       (5,'APPLE',360,3000),
       (5,'POTATO',2000,3000),
       (5,'TOMATO',360,3000),
       (5,'BANANA',123,3000),
       (5,'ORANGE',126,3000);

What I want is to find those order_nums that have different product_names so that no two product_name for one order_num should be the same . The desired result would be (2,3,5). I tried to solve this with Exists but it won't return the correct result.
SELECT *
FROM A_TEST A
WHERE EXISTS (SELECT *
              FROM A_TEST B
              WHERE A.order_num = B.order_num
              AND A.product_name <> B.product_name);

I know why this does not return the correct result. The reason is that if it finds at least one product_name which is different from another one, it will return it's order_num. I was wondering if you could suggest better solutions.
Thanks in advance


texts: Why Table Lock Escalation is happening in my scenario

texts:
I have a table Table1 that is updated in small chunks using below query:

update top (1000) Table1
    set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
      and VarcharColumn1 is NULL

Additional details:

Table2 has 90000 rows, and total number of rows that need to be updated in Table1, is also 90000 (1 to 1 relationship)

Also when Table1 rows are updated, there is a trigger on Table1 that inserts rows as they were before update, to table Table1History
So this means when I update 1000 rows in Table1, 1000 rows inserted into Table1History

Question:

When I update top 100 rows, no Table Lock Escalation happens

I monitor this using Extended Events "lock_escalation" event, and also in Performance Monitor - SQLServer:Access Methods - Table Lock Escalations / sec

When I update top 1000 or 500 rows, Table Lock Escalation IS happening on Table1

So I wonder, what is the mechanism or formula that is used by SQL Server
to escalate locking to table level ?

In google it says 5000 rows is the threshold, but obviously in my case 1000 or 500 rows is enough to cause Table Lock Escalation on Table1


texts: How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?

texts:
In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions:

  Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation.

What magnitude of number of rows or number of data pages is considered around the cutoff of where it's worth considering index reorganize or index rebuild operations?


texts: How do I create a Mongo config file to allow access to http on port 28017

texts:
Well I want to configure the mongo server to allows access for http.

I put this on my mongo config file:

port = 28017
rest = true

And when I restart my mongod service and try to access I got this message on the browser.

  It looks like you are trying to access MongoDB over HTTP on the native
  driver port.

I check my logs:

cat /log/mongod.log
2015-08-07T18:07:27.430+0000 [initandlisten] MongoDB starting : pid=20333 port=28017 dbpath=/data 64-bit host=ip-x-y-z-d
2015-08-07T18:07:27.430+0000 [initandlisten] db version v2.6.10
2015-08-07T18:07:27.430+0000 [initandlisten] git version: 5901dbfb49d16eaef6f2c2c50fba534d23ac7f6c
2015-08-07T18:07:27.430+0000 [initandlisten] build info: Linux build18.nj1.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-08-07T18:07:27.430+0000 [initandlisten] allocator: tcmalloc
2015-08-07T18:07:27.430+0000 [initandlisten] options: { config: "/etc/mongod.conf", net: { http: { RESTInterfaceEnabled: true, enabled: true }, port: 28017 }, processManagement: { fork: true }, security: { authorization: "enabled" }, storage: { dbPath: "/data" }, systemLog: { destination: "file", path: "/log/mongod.log" } }
2015-08-07T18:07:27.434+0000 [initandlisten] journal dir=/data/journal
2015-08-07T18:07:27.434+0000 [initandlisten] recover : no journal files present, no recovery needed
2015-08-07T18:07:27.778+0000 [initandlisten] waiting for connections on port 28017
2015-08-07T18:07:27.778+0000 [websvr] admin web console waiting for connections on port 29017

I refresh the browser and the logs show me that:

2015-08-07T18:24:09.246+0000 [initandlisten] connection accepted from 181.196.26.130:64683 #24 (1 connection now open)
2015-08-07T18:24:09.246+0000 [conn24] end connection IPAddress:64683 (0 connections now open)
2015-08-07T18:24:09.493+0000 [initandlisten] connection accepted from 181.196.26.130:53343 #25 (1 connection now open)
2015-08-07T18:24:09.493+0000 [conn25] end connection IPAddress:53343 (0 connections now open)

So If the log says that it waiting for connection on that port give me a respond but I can't see the http admin view, any idea or tips?


texts: Can I set PostgreSQL "stringtype=unspecified" behavior as default?

texts:
I am trying to get Hibernate to save Java enum fields as enum columns in PostgreSQL 9.6. If I map the fields as @Enumerated(STRING), Hibernate naively uses the enum name as the column contents. This works splendidly for reads (it just gets the string representation, matches up and returns), but the server complains about the type mismatch when writing: expression is of type character varying.

The PostgreSQL JDBC driver has a parameter stringtype that will tell the server to infer the appropriate column type for string values, and using stringtype=unspecified successfully gets enum values stored. However, I will be deploying my application in an environment where the JDBC connection string is supplied externally, and it's not practical to add the stringtype property to it.

Is there a way I can configure the server (such as with a SET command) to treat connections as stringtype=unspecified by default?


texts: Replicate some tables without transferring the entire log

texts:
I have a mysql database that contains some tables with private information, and some tables with public information.

I would like to replicate only the tables containing public information from one database to another, making sure that NO confidential information ever gets stored on the slave.

I know I can use the replicate-do-table to specify that only some tables are replicated, but my understanding is that the entire bin log is transferred to the slave.

Is there a way to ensure that only the public information is transferred to the slave?

I am very hesitant to add another copy of the database to an existing server - I just don't think the existing server has the available capacity, in either RAM or CPU.


texts:
Actualy I'm using MongoDB with embedded images next to other data in same documents.
Images count actualy are more than 14000 and each image size is less than 1MB. Images are readed frequently and updated rarely.

I want to separate images from data and store it in different database than data to use it as microservice and for scalability. I've read people saying that the best solution to store images is on file system, but you have limitations about number of files in folder and in a lot of CloudComputing services you don't have access to the file system. In MongoDB official documentation, they say that GridFS is faster than file system, but I've understood that the most case to use GridFS is when you have files with size more than 16MB.

Do you know if there are some specific database to store files?
If not, what conventional database is more efficient to store files?
Is MongoDB a good solution to store files? If yes, what is better for the size of my images, storing as normal documents in collection or as GridFS?

Thanks


texts:
I'm using Xtrabackup to perform a MySQL DB restore.

I found some problems when I'm trying to restore individual tables: tables with InnoDB engine can be restored without any problem but I can't find a way to backup and restore MyISAM tables.
Is there a way to restore MyISAM individual tables with Xtrabackup or similar products?


texts: List default column values for all tables in a database

texts:
I would like to list the default values for all columns in all tables in an Oracle database. The objective is to review the default values, and find any that need updating.

There are hundreds of tables in the database with columns that have default values, so it would be preferable to do this programmatically, rather than manually.

How can I do this?


texts: How to automate basic ddl scripts execution and management

texts:
Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually Oracle and MySql

Problem statement: Adding a new column via ALTER TABLE to an existing table. Current process is to send the script to pool of DBAs who get hundreds of such requests from various venues daily. Have to wait for them to pick up the request and then respond which usually takes a day or two. They have to manage that script going forward and execute it in Dev, QA(multiple QA environments) & ultimately PROD. In this process the script is usually lost after PROD and cannot be traced back as to who and why they created a change.

Looking for: Suggestions on how this can be automated. Are there any standard tools or software which can manage scripts across environments. Database is Oracle and MySql.


texts: adding autoincrement to id already part of foreign key

texts:
I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without dropping the foreign key constraint for all of them, adding the autoincrement property and re-creating the foreign key constraints?

Thank you very much!


texts:
What are the best practices with setting parallelism in general?  I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default behavior?

I remember reading somewhere (I'll have to look for this article) that for OLTP workloads you should turn off parallelism (set maxdop to 1).  I don't think I completely understand why you would do this.

When would you keep maxdop up to SQL Server (0)?
When would you turn off parallelism (1)?
When would you explicitly state the maxdop to a particular number of processors?

What causes parallelism?


texts:
I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA.  I have created tables using the XMLTypes generated.  I can create XML using XMLQuery and if I understand correctly, the query results can be inserted into the table if the definition is correct.

Ultimately the XML needs to be written to a file, but the part I am unsure about is if writing XMLQueries from scratch is the best way to generate the XML.  Is there any way to have the XML schema generate an XML stub for a guide or generate template XMLQuery?  The data is currently all in regular Oracle tables, but arranged quite differently, so any information on easing the export to XML based on a specific set of registered schema would be useful.


texts: how to take backup of a child table with foreign key dependency in mysql using bash

texts:
Parent table : p_id, t_id, name , salary
Child table : c_id, t_id , age
key : Child.t_id=Parent.t_id

While taking backup i would like to take pull only the matching records from Child table . How can i do it via bash script ?

Thanks in advance .


texts: speed up data transfer from SQL server to application

texts:
I have build an application that handles data stored in a MS SQL server database.
This server is hosted externally of our company and has to be connected to via SSH because of company rules.

On a regular working day it takes about 7 minutes to retrieve a list of data, while it takes less than one minute to do the same outside office hours.

The database is relatively simple. There are 3 main tables which have a relation to each other on one field which is also set as primary key.
These tables have relations with several other small tables where lists are stored.
So the fields in the main tables are integer fields related to a small table where that integer points to a text field.

I'm trying to figure out why it takes 7 minutes during office hours and less than one 1 outside office hours.

There are about 12 users.

Anyone have some tips for me?

rg,
Eric


texts: Row vs Columnar DB for queries with multiple OR statements

texts:
I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements?
For instance, I want all the records that have (Val A, Col A) OR (Val B, Col B) OR (Val C, Col C) OR...(Val N, Col N)
I hope I'm asking that clearly.
Edit:
The query OR statement would look A = 1 OR B = 3 OR C = 6 ... OR N = 7
And the reason I'm doing this is I would have a row/record where each col/field is encrypted and I'd like all the records that have a match with any of the fields for a clustering exercise. This query could run 100s of times a second.


texts: What can be used with SQL Server 2014 native tools, for Business Inteligence?

texts:
We're trying to achieve better reports using native tools from SQL Server 2014.

I'm a DBA, and I don't know so much about c#, c++ and etc.

I know also, that we have Analysis services, SQL Server Data tools, but, long story short, programmers here where I work don't care about anything. They don't want to learn.

Is there a way to help my company, being a DBA ? I love to learn but I have some doubts about what service I can use to achieve reports with graphics, and those reports that bosses love.

If this is a bad question I can delete it with no problem.

My past job, we had an awesome application that uses views and procedures to ( I helped them with views and procedures and query tunning, but not with Visual Basic code ), in real time, update graphics and etc on a web page ( it was awesome ) but we can make it here, because as i said, they don't know and don't have the motivation to learn.


texts: SQL query to check a table against a "rules table"

texts:
I have a table PIT that looks like this:

PIT_TYPE    PIT_STYPE   PIT_APID
1           1           1313
1           7           1313
3           25          1316
247         1           1318
247         2           1318
247         4           1318
247         5           1318
5           2           1319
5           3           1319

I have a rules table that defines the rules for PIT. The rules table looks like this:

Type    SType   Category
1               1
3         26    8
3         25    5
3         25    4
5               7
6               9
9               0
13             10
17         2    7
21             12
22             11

PIT_APID mod 16 = Category E.g. 1313 mod 16 = 1

The rules specify following:

If PIT_TYPE / PIT_SType match no rule in the rules table --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table and the category matches --> No error
If PIT_TYPE / PIT_SType match a rule in the rules table but the category is different --> Error

(If SType is empty in the rules table we don't care about the PIT_STYPE in PIT. Type is never empty)

How can I write a SQL query that returns all the rows in PIT that have an error according to the defined rules?

What I have tried is:

SELECT PIT.PIT_TYPE, tblCategoryRules.Service, PIT.PIT_STYPE, tblCategoryRules.Sub_Service, PIT.PIT_APIT , PIT.PIT_APIT MOD(16) as PCAT, tblCategoryRules.Category
FROM PIT
INNER JOIN tblCategoryRules
ON PIT.PIT_TYPE =  tblCategoryRules.Service
WHERE  (tblCategoryRules.Sub_Service IS NULL or  tblCategoryRules.Sub_Service = PIT.PIT_STYPE) AND tblCategoryRules.Category <> PIT.PIT_APIT MOD(16)

This works for everything except the rule that

Type    SType   Category
3         25    5
3         25    4

The rule that it can either be 5 or 4 is what causes the problem


texts: Error during Oracle database 12c Release 1 installation

texts:
I am trying to install Oracle database 12c on Oracle Linux 7 version. But it is keep on failing with the below error.

[INS-30131] Initial setup required for the execution of installer validations failed.

CAUSE: Failed to access the temporary location.

ACTION: Ensure that the current user has required permissions to access the temporary location.

ADDITIONAL INFORMATION:

Framework setup check failed on all the nodes
Cause: Cause Of Problem Not Available
Action: User Action Not Available

Summary of the failed nodes
ravilinux

Version of exectask could not be retrieved from node "XXXXXX"
Cause: Cause Of Problem Not Available
Action: User Action Not Available****

I see solutions are available for this error in Windows. But I don't see a solution for linux.

Can someone help on this please? I am trying to resolve this error for a week now.

I have /tmp with 3.9gb allocated. It is Free with 3.6GB still.


texts: postgres automatic table list partitioning

texts:
How to handle new partitions in a smart automatic manner in PostgreSQL ?

I want to create a PostgreSQL table List partition on a certain column.
For example:

CREATE TABLE orders
(
  id            int ,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (id)
) PARTITION BY LIST (country_code)

however I dont know all the possible counties in advance, on an oracle table I will enable auto partitioning:

ALTER TABLE orders SET PARTITIONING AUTOMATIC;

It seems like PostgreSQL lacks this feature, I have read that I can add a default partition to the table and then later on add another partition and the values from the default partition would be transferred automatically to the new partition. (EDIT: default partition block you from creating new partitions if you previously inserted values to it)

what would be the best strategy to handle this in an automatic manner ? Are there any plans to add this feature to PostgreSQL?


texts: Execution plan for select with empty value using wildcards

texts:
Short question: with this: foo like '%%', MySQL go through all records or through none?

If you are thinking why i need this, it's because i'm concatenating a search string and if this have no performance problem, i have to write less code.


texts: Oracle - query by two column combination

texts:
I have two tables:

    product_table:
    ------------------------------------
     product | market | category | other
    ------------------------------------
             |        |          |
    ------------------------------------

    availability_table:
    -------------------------------------
     product | num_available | some_other
    -------------------------------------
             |               |
    -------------------------------------

I have a list of 7 markets and a list of 41 categories. For each market/category combination, I need to get several products (3 is a good number) that are currently available. I came up with a query that delivers products for one market/category combination:

SELECT product_table.product, product_table.category
FROM product_table, availability_table
WHERE   product_table.market = 1 // market is changing from query to query
    AND product_table.product = availability_table.product
    AND availability_table.num_available > 0
    AND product_table.category = 'category_1' // category is changing from query to query
    AND ROWNUM <= 3

This works well for one market/category combination, but I have to manually submit 287 queries to get all data I need. Is there any way to build a query that will do it for me with one submission?


texts: Does restoring an Oracle backup of the whole database restore schema objects?

texts:
I used RMAN with,

RMAN>backup database archivelogs all;

After that I truncated a table.

I then tried to restore with,

RMAN>restore database;
RMAN>recover database;
RMAN>alter database open;

I connected with sqldeveloper, and the table is still empty.  I have tried many times.  I made sure there wer archive files with crosscheck backup.  It simply never restores the data to that table in the schema.  I know you cannot restore a single table, so I restored the whole database thinking it would put my data back.

There is one database, one backup, truncate, one restore (did this a lot).  I have tried set until for an incomplete backup, many things.  I don't need a point in time, however.  I was trying to get anything to work.

I received no errors.  Everything looked good.

(edit)

All disk backup.
Oracle Enterprise Linux 6.5.
Oracle 11gR2, 11.2.0.3

(edit)

I have not used logminer.

What am I doing wrong?


texts: SUM n rows of each group with ORDER BY and LIMIT where the LIMIT is based off of another table

texts:
I have a requirement to build a leaderboard of users of a fantasy football game.
The simplified database of the game is as follows:

users have squad_players in squads for each matches (related by transfer_period)
players have match_points for each matches
squad_players have position, priority. The priority is the order of substitutions if a squad_players does not appear in a matches
squads have formations which determine the maximum number of players to select from of each position ordered by priority

The database is MySQL 5.6 and the maximum number of users is 10K.

I am able to inner join (in order) squad_players, squads, matches, match_points to get the point of each squad_players who played (players who didn't play have no match_points).

I am struggling to SUM the points of x squad_players of each squads where x is in formations and determined by position of the squad_players.

I have tried to modify versions of Top n-rows with group by with correlated subqueries but without success.

What should my approach be here?

My Expected Output is something like this

|---------|-----------|------------|----------|
| User ID |  SQUAD ID | POSITION   |  POINTS  |
|---------|-----------|------------|----------|
|   12    |    34     | defender   |    12    |
|---------|-----------|------------|----------|
|   12    |    34     | forward    |    2     |
|---------|-----------|------------|----------|
|  117    |    31     | midfielder |    5     |
|---------|-----------|------------|----------|

Notes:

I must add the outputs may not reflect the problem statement.
If you look into the Sample Dataset Link, you will find a view
named normalized_squad_player_points that is the denormalized table I want to work with to create different stats.

Final Output for the leaderboard will be something like this

|---------------------|------------------|
|      User ID        |       Points     |
|---------------------|------------------|
|          12         |         34       |
|---------------------|------------------|
|          56         |         32       |
|---------------------|------------------|
|          117        |         31       |
|---------------------|------------------|

Additional information

SQL Fiddle link to schema.
Link to exported Schema and Sample Dataset.
I'm open to modifying table definitions if necessary.
Pastebin link to squads and match_points inserts.


texts: Is there a way that I can export a list of all table triggers on a server?

texts:
I have two SQL Server instances that I manage.  One is a SQL Server 2000 instance, and the other is 2005.

Some where, on these servers, I recall setting up a couple table triggers that were executed under certain conditions.  I need to look-up these triggers as a point of reference for a new project, however, for the life of me I can not find them.

Is there some crafty SQL statement that I can execute that can iterate over all my databases and, subsequently, iterate over each table and output any triggers associated with the tables?


texts:
I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how they work.

The articles I have read online are giving me a little trouble and I am finding it hard to understand. It would be great if someone could, maybe, kindly explain it in simple words what they are and what they do?

Thank you so much for the help!


texts: Is there a service broker target for an extended event session

texts:
We use Service Broker heavily for asynchronous background processing.
Additionally we want to use it for extended event processing.

Is it possible to use Service Broker listener as target for an extended event session?


texts: How to enforce the structural constraints of rectangularly arrayed data?

texts:
(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and "many-to-many"?)

Much experimental data in biomedical research is collected in "plates" of rectangularly arrayed "wells".  These well array plates are commercially available a few standardized sizes: 2 × 3, 4 × 6, 8 × 12, 16 × 24, and 32 × 48.

Consider the following two alternatives for storing measurements from wells of 2 × 3 plates in an RDB:

-- alternative 1
CREATE TABLE measurement_foo (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    plate_row CHAR(1),
    plate_column INTEGER,
    value REAL
);

-- alternative 2
CREATE TABLE measurement_foo (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    a1 REAL,
    a2 REAL,
    a3 REAL,
    b1 REAL,
    b2 REAL,
    b3 REAL
);

My instinct is to go with alternative 1: it generalizes to plates of any size, and it can be modified in a straightforward way to record multiple different measurements per well, as in

CREATE TABLE measurement (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    plate_row CHAR(1),
    plate_column INTEGER,
    foo FLOAT,
    bar FLOAT,
    baz FLOAT
);

In contrast, to adapt alternative 2 to the 16 × 24-well format (for example), would entail defining a table with 1 + 384 columns: plate_id, a01, ..., a24, b01, ... , b24, ..., p01, ..., p24.  Moreover, a different such table would need to be defined for each type of measurement.

My main reason for even considering alternative 2 is that it alone enforces the constraint that each plate contains exactly 6 wells; alternative 1 does not.

(Also, one record of the table in alternative 2 would hold as much information as up to 6 records of the one in alternative 1, which makes me wonder whether the table in alternative 1 is fully normalized.)

For alternative 1, I can imagine ways to enforce the weaker constraint that a plate contains at most 6 wells, namely, by imposing a uniqueness constraint on (plate_id, plate_row, plate_column), and restricting columns plate_row and plate_column to the domains ('a', 'b') and (1, 2, 3), respectively.  But it is still possible for wells to be missing from the data.

How should the database enforce the structural relationship between plates and wells?


texts: Remove a character from a records enclosed between double quotes

texts:
I have a table in which every row have data like:

0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014

What I want is to remove every comma (,) that is enclosed between double quotes " ". And then split the rest of the string with comma (,)

I don't want to do it checking every single character setting flags for start and end of double quotes.

Can i implement some sort of regex?

Is there a simple way?

What I have tried so far is just to split the string based on the comma (,) but it is also splitting values inside the quotes.

This is serving the purpose: how can I implement this in complete table (currently it is working if I have only one instance of double quotes block)?

Declare @Query nvarchar(max)

Set @Query = 'Item1,Item2,"Item,Demo,3",New'

Declare @start int, @len int
SELECT @start = PATINDEX('%"%"%', @Query) + 1

print @start

select @len = CHARINDEX('"', SUBSTRING(@Query, @start, LEN(@Query))) - 1

select
        SUBSTRING(@Query, 1, @start - 2) +
        REPLACE((SUBSTRING(@Query, @start, @len)), ',', '') +
        SUBSTRING(@Query, @start + @len + 1, LEN(@Query))

This is the function I am using to split

ALTER FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (id int, item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
Declare @ID as int
Set @ID=0
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 Set @ID=@ID+1
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @ID,@sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @ID,@sInputList -- Put the last item in
RETURN
END

The problem is that this is an application in MS Access, which narrows down my possibilities.  All I can do is passing the name of the file which is going to get imported by SQL Server.


texts: How do I select a string valued field against a number less than X?

texts:
There are two field formats. One is SOMENAME1234 and another is SOMENAME1234_SE. What I need to do is select all of the records where the number 1234 is less than X. The application is a test database where Jenkins generates unique schema names to keep our developer's tests from crashing into each other. The value '1234' represents a build number.

What I'm trying to write is the cleanup script. Problem is that I need to make sure that I'm not removing any schemata that are currently in use. So I found a way to pragmatically hit Jenkins for what is currently building. Such that now I have a value X that represents the lowest build number currently building. I can delete any schemata with a value lower than this. Problem is that I don't know how to write that SQL. Looking at REGEXP_LIKE, I see that there might be a regular expression involved.

Currently I have:

SELECT username
FROM dba_users
where username NOT LIKE '%"+upperBound+"%'
AND username LIKE 'BLDXYZPDQ%'

This is the best I've been able to do so far.

What I intend is to select the list and then drop each schema in that list one at a time. That way I need not worry about new schemata created during the process.


texts: Getting by list of ids: `unnest() JOIN` vs `= ANY()`


texts: Your PHP MySQL library version 5.5.44 differs from your MySQL server version 10.0.20. This may cause unpredictable behavior

texts:
How can I fix this "error" in phpmyadmin ? Is this a serious problem, I should care about?

Server Version: 10.0.20-MariaDB-1~trusty-log - mariadb.org binary
distribution Apache/2.4.7 (Ubuntu)
Datenbank-Client Version: libmysql 5.5.44
PHP-Erweiterung: mysqli Dokumentation
All packages are uptodate (official ubuntu 14.04 repos)
On MariaDB I use the official repos from mariadb.org

Thanks in advance!


texts: Postgresql - Point in Time recovery using recovery.conf and trigger. How to minimize the time waiting for .history files

texts:
Following http://www.postgresql.org/docs/9.1/static/continuous-archiving.html, I copy the WAL archives to another machine and apply them regularly. Recently due to pg server crash at my main server I tried to restore the database by pushing a trigger (an empty file, expecting smart failover) and the logs are given below.

prodrestore_error.log

WAL file not present yet. Checking for trigger file...
trigger file found: smart failover
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 0000000100000001000000CC
WAL file path:        /backup/prod/db_backup/archive/0000000100000001000000CC
Restoring to:         pg_xlog/RECOVERYXLOG
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/0000000100000001000000CC" "pg_xlog/RECOVERYXLOG"
Keep archive history: 000000000000000000000000 and later
trigger file found: smart failover
running restore:      OK

Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000002.history
WAL file path:        /backup/prod/db_backup/archive/00000002.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000002.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
not restored
history file not found
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000001.history
WAL file path:        /backup/prod/db_backup/archive/00000001.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
not restored
history file not found

postgresql-9.1-main.log

2015-08-03 08:08:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:11:44 IST::@:[5542]:LOG:  could not open file "pg_xlog/0000000100000001000000CD" (log file 1, segment 205): No such file or directory
2015-08-03 08:11:44 IST::@:[5542]:LOG:  redo done at 1/CC001E10
2015-08-03 08:11:44 IST::@:[5542]:LOG:  last completed transaction was at log time 2015-08-03 07:59:58.121908+05:30
2015-08-03 08:11:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:17:44 IST::@:[5542]:LOG:  selected new timeline ID: 2
2015-08-03 08:23:45 IST::@:[5542]:LOG:  archive recovery complete
2015-08-03 08:23:51 IST::@:[6088]:LOG:  autovacuum launcher started
2015-08-03 08:23:51 IST::@:[5541]:LOG:  database system is ready to accept connections

The whole process of looking for two history files 00000002.history and 00000001.history, 4 times and finally start the database took approximately 12 minutes. Then each repeat iteration by restoring a base backup fresh took more time as it started looking for more .history files.

How I can make this as fast to look for history files and if not present proceed quickly to start database in read/write mode ?  Else, not to look for history files at all.


texts: Why isn't the rows of an EXPLAIN accurate?


texts:
I am doing my first ssis and sql project for uni being new to both. I am creating a date dimension but only month and year no days. I have seen loads of examples online for date dim but including days. Is there a way to create monthly date dim as deadline is approaching and still a lot to do. All help much appreciated.

Cheers

Aidan


texts: Postgresql: How to index jsonb array of objects column

texts:
I have a table that has a jsonb column which has an array of objects.

Looks like this for every row.

[{grade: 'A', subject: 'MATH'}, {grade: 'B', subject: 'PHY'}...]

Now querying over it thanks to this post https://stackoverflow.com/a/30592076/2405689 .

Problem comes it takes at least 2.4 secs to finish querying a count of all students with grade IN (A, B, C).

I would like to get some help on indexing this because, the indexes I have tried didn't do anything.

DROP INDEX idx_subjects_subject;
DROP INDEX idx_subjects_grade;
CREATE INDEX idx_subjects_subject ON results USING GIN((subjects-> 'subject'));
CREATE INDEX idx_subjects_grade ON results USING GIN((subjects-> 'grade'));

Also did (separately):

DROP INDEX idx_subjects_standard;
CREATE INDEX idx_subjects_standard ON results USING GIN(subjects);

I'm querying them like this.

SELECT COUNT(*)
FROM results
WHERE EXISTS
    (
        SELECT 1
        FROM jsonb_array_elements(subjects) AS j(data)
        WHERE (data #>> '{subject}') LIKE '%MATH%'
        AND
        (data #>> '{grade}') IN ('A', 'B', 'C')
    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

I also tried querying it like this :

SELECT COUNT(*)
FROM results
WHERE EXISTS
    (
    SELECT 1
    FROM jsonb_array_elements(subjects) AS j(data)
    WHERE data @> '{"subject": "B/MATH", "grade": "A"}'
    OR data @> '{"subject": "B/MATH", "grade": "B"}'
    OR data @> '{"subject": "B/MATH", "grade": "C"}'

    )
AND
    "examYear" = '2010'
AND
    "examType" = 'CSEE'
;

But this gave me a reversed effect (3 secs query).

Here is my explain analyze block.

"Aggregate  (cost=1888617.19..1888617.20 rows=1 width=8) (actual time=2517.116..2517.117 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on results  (cost=470767.18..1888021.96 rows=238090 width=0) (actual time=680.456..2514.633 rows=24002 loops=1)"
"        Recheck Cond: ("examYear" = '2010'::text)"
"        Rows Removed by Index Recheck: 557945"
"        Filter: (("examType" = 'CSEE'::text) AND (SubPlan 1))"
"        Rows Removed by Filter: 500054"
"        Heap Blocks: exact=40156 lossy=53452"
"        ->  Bitmap Index Scan on idx_results_subjects  (cost=0.00..470707.66 rows=528405 width=0) (actual time=672.375..672.375 rows=524056 loops=1)"
"              Index Cond: ("examYear" = '2010'::text)"
"        SubPlan 1"
"          ->  Function Scan on jsonb_array_elements j  (cost=0.00..2.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=458487)"
"                Filter: (((data #>> '{subject}'::text[]) ~~ '%MATH%'::text) AND ((data #>> '{grade}'::text[]) = ANY ('{A,B,C}'::text[])))"
"                Rows Removed by Filter: 8"
"Planning time: 0.126 ms"
"Execution time: 2517.145 ms"

I'm on postgresql: 9.6.1


texts: Determine Oracle package body memory size

texts:
We have recently added a lot of new packages to our system, and trying to determine incremental memory usage from a prior version of our product. I know I can get the sharable_mem value from v$db_object_cache.  However, that will not include all packages in our schema, as they may be aged out.

Is there any way to determine the memory size of a package that is not in the object cache?  And, I do not mean lines of source code (ALL_SOURCE), but size of whatever it is that oracle does under the hood to compile/parse the package.

Thanks!


texts:
I have Linux RedHat Enteprise with Oracle 11g database and I've stumbled upon a circle of errors and I can not find a way out.

First error in alert_XE.log:

ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01/o1_mf_1_580_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2940.trc:
ORA-16038: log 2 sequence# 580 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dh40n8hk_.log'
System state dump requested by (instance=1, osid=2940), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_2859.trc
USER (ospid: 2940): terminating the instance due to error 16038

Which would be easily solveable with rman, but here comes error number 2 when "connect target /" on rman:

ORA-01031: insufficient privileges

Which is of course again solveable with orapwd file. I've created a new file with the right SYS password. Trying again to login via rman, but of course, no luck.

I've tried also with sqlplus with various commands like "sqlplus sys/@XE as sysdba" and I get error number 3:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I've tried "tnsping XE" and it looks ok:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

Any ideas how to proceed?


texts: Postgresql table with one integer column, sorted index, with duplicate primary key

texts:
I want to have a table in PostgreSQL with these properties:

One column with an integer
Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be deleted by a WHERE clause on the integer. The table is used like a set.
Efficient ORDER BY+LIMIT on the column/rows, and DELETING with a WHERE-clause for that integer
INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID. (That's why duplicates must be allowed, the check for primary keys would cause more disc seeks and can fail transactions and cause deadlocks)
INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.
There shall not be additional btree files for this table, so no secondary indexes
The rows should occupy not much space, e.g. have no OIDs, so that many fit in one page.

I cannot think of a solution that solves all of this.

The easiest thing would be a PRIMARY INDEX on that one column. But this won't allow duplicates.

Also I could use a secondary index, but this would cause more operations per INSERT and more disc access. I am really bound by disc seeks here.

Currently my best solution would compromise on the last bullet point: I could just a PRIMARY KEY covering the integer column and also a dummy column, like an OID, a timestamp column or a SERIAL column. Then every row (=primary key) would be unique. But this would increase disc space and reduce the amount of rows per page.

Is there any chance of having a kind of primary key together with duplicates? Can I control what the main btree file on disc is organized by?

(I posted a related question like this question on StackOverflow, but I was very unclear about my goals. I hope this doesn't count as spam, this question here is completely different)


texts: Postgres tablespace size is bigger than database size

texts:
PostgreSQL tablespace size is bigger than database size. What is the problem about that?

SELECT pg_size_pretty(pg_database_size('ibidev'));
 pg_size_pretty
----------------
 2578 MB
(1 row)

SELECT pg_size_pretty(pg_tablespace_size('tbs_ibi_dat'));
 pg_size_pretty
----------------
 47 GB
(1 row)

Using PostgreSQL 9.1


texts: Fetch items where related rows has different values

texts: I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. It means like I have one attribute for 5 different contexts, and I want fetch only those attributes where value_c is different. I know how to fetch all of them but how add comparison? Can somebody help me? Thanks!


texts: Query involving TVF very slow on SQL Server 2008

texts:
I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because it's a linked server or because it's 2008. I found a blog post about TVFs confusing the query optimizer on 2008, but I'm not sure how to apply that info to my situation.

Here's the query on the local table:

select 'E10', InvoiceNum, MiscCode, Description, DocMiscAmt, Type
from Live.Erp.InvcMisc
where InvoiceNum in (
    select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 5, default, default, default)
    where DB = 'E10'
)
and MiscCode not like '%SHI'
and MiscCode not like '%HAN'

plan

Here's the query on the linked table. The difference in the plan is obvious:

select 'E9', InvoiceNum, MiscCode, Description, DocMiscAmt, Type
from tmiedb.Epicor905.Dbo.InvcMisc
where InvoiceNum in (
    select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 3, default, default, default)
    where DB = 'E9'
)
and MiscCode not like 'SH%'
and MiscCode not like 'HD%'
and MiscCode <> 'THDL'

plan

I got rid of the loop by rewriting it like this. This runs in about 1 second:

select 'E9', InvoiceNum, MiscCode, m.Description, DocMiscAmt, Type
from dbo.UnifiedSalesJournal(2018, 3, default, default, default) i
left join tmiedb.Epicor905.Dbo.InvcMisc m
on i.ARInvoiceNum = m.InvoiceNum

plan

But if I add a where clause, this takes 29 minutes.

The data is small. The TVF returns a few thousand rows. The local table contains 10K rows, and the linked table contains 55K rows.

The linked server contains historical data that will never change, so I'm considering importing the whole database into SQL Server 2014. But I'm hoping there's a less drastic solution.

Possibly related: Linked Server behavior. I'm seeing the behavior described in Case 3a. It's calling sp_execute thousands of times, once for each row returned by the TVF.


texts: SSIS proxy/credentials not working from within SQL Agent job step

texts:
I have been looking around and found similar questions but nothing specific to what I am having trouble with.
Problem:
I cannot get the SSIS proxy and SQL credentials configured correctly to impersonate a restricted-access service account for a SQL Agent job step that runs an SSIS package. The restricted-access service account has all the needed privileges and the package is designed correctly. I have read much material on this subject and would like some help in case I missed something.
Background:
(SQL 2012 SP3 server running on Windows Server 2012 R2.
SQL engine runs under Domain1\Admin1 and SQL Agent runs under Domain1\Admin2. both also are in the SYSADMIN server role.)
We have an SSIS package that works fine when run interactively by a SYSADMIN, and also runs fine when run within a job step as "SQL Server Agent Service Account". However our Security group wants us to run using credentials limited to what the job needs to do, and I understand this as a best practice anyway. Everything I have read indicates that an SSIS proxy and credential will help solve for this requirement; however I can't get the configuration to work so I must be doing something wrong.
The package is run via SSIS package job step. It is a local file and is not deployed to MSDB or SSISDB. The package connects to a network share, loads into a database that is local to the SQL server, truncates a table, runs a few stored procedures, then deletes the XLS file. The job step is set to use the 32-bit runtime.
Security created an account (Domain1\NewUser) that will have Modify rights to the network share. I also asked they add Domain1\Admin2 as Modify to the same share.
For setup, I created a SQL login for Domain1\NewUser using only the Public role, and added it as db_owner for the database in question because of the range of actions the package needs to perform. I created a credential (BatchLoad-credential) using as its identity the Domain1\NewUser account and the exact working password of the user account. I then created an SSIS proxy (BatchLoad-Proxy) using the BatchLoad-credential credential, active within the SSIS package subsystem, and added the SQL login for Domain1\NewUser as a proxy account principal. Then I changed the SQL Agent job step running the SSIS package to run as the BatchLoad-Proxy, and changed the owner of the job from Domain1\Admin2 to the Domain1\NewUser account.
When the job is run, we get this error (some masking on the log has been done):

Executed as user: Domain1\NewUser. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  12:46:17 PM
Error: 2016-06-24 12:46:28.56   Code: 0xC0202009     Source: xxxxxxx_xxxx Connection manager "yyyyyyyyyyy"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".  End Error

Again, the package works fine when a SYSADMIN runs it interactively within BIDS so we know the file is in the correct format and the package is designed ok.
I do not have permissions to the network share. When I try to connect manually I get a similar error:
Windows cannot access
\\<path to the network share>
Error code: 0x80004005
Unspecified error

Connecting to the network share using the Domain1\NewUser account and password, I can view the folder ok so I have verified the proper user account has access.


texts: Why does 10^37 / 1 throw an arithmetic overflow error?

texts:
Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code:

DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);

PRINT @big_number + 1;
PRINT @big_number - 1;
PRINT @big_number * 1;
PRINT @big_number / 1;

The output I get for this code is:

10000000000000000000000000000000000001
9999999999999999999999999999999999999
10000000000000000000000000000000000000
Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type numeric.

What?

Why would the first 3 operations work but not the last? And how can there be an arithmetic overflow error if @big_number can obviously store the output of @big_number / 1?


texts: PostgreSQL queries from external IP causing high CPU load. Have I been hacked?

texts:
Since I few days I have noticed that on my Atlassian Software VM (running only Jira, BitBucket and Bamboo) PostgreSQL is causing very high load as seen in htop.

I then checked with

SELECT * FROM pg_stat_activity;

what is running on my system and found two weird entries below.

12405 | postgres    | 15526 |       10 | postgres   |                  | 114.86.94.107 |                 |       56033 | 2017-11-17 15:40:03.745147+00 | 2017-11-17 15:40:04.954341+00 | 2017-11-17 15:40:04.954341+00 | 2017-11-17 15:40:04.954343+00 |                 |            | active |             |      3202367 | select Fun020001 ('./x4000021328 &')
12405 | postgres    | 15539 |       10 | postgres   |                  | 114.86.94.107 |                 |       61953 | 2017-11-17 15:40:12.560015+00 | 2017-11-17 15:40:13.374722+00 | 2017-11-17 15:40:13.374722+00 | 2017-11-17 15:40:13.374724+00 |                 |            | active |             |      3202368 | select Fun020001 ('./ps3991599979 &')

The queries they're running seem weird to me

select Fun020001 ('./x4000021328 &')
select Fun020001 ('./ps3991599979 &')

I then found files named like the two above in

/var/lib/postgresql/9.6/main/

-rwxrwxrwx 1 postgres postgres    14480 Nov 17 15:39 ps3991599979
-rwxrwxrwx 1 postgres postgres   496464 Nov 17 15:40 x4000021328

Do I assume correctly that my machine has been hacked? For now I have disabled remote connections and deleted the binaries. I also turned off the machine. Is there anything else I can do to prevent further abuse of my VM?


texts: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

texts:
In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error:

  UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).

An earlier question suggests that stored procedures must be created with QUOTED_IDENTIFIER set ON. In my case, however, there are no stored procedures. One example Agent job has one step and that contains straight SQL to carry out two updates - each update is against the table I altered. None of that SQL contains a double-quote character (which is the subject of QUOTED_IDENTIFIER).

Microsoft documentation says:

  SET QUOTED_IDENTIFIER must be ON when you are creating or changing
  indexes on computed columns or indexed views.

... however I simply added the column (persisted). I have not created or altered any indexes.

Why am I getting this error in this scenario?
How do I resolve this? Do I need to re-create all the Agent jobs, switching QUOTED_IDENTIFIER ON before I do? Or do I need to switch QUOTED_IDENTIFIER ON before altering the table to add the computed column?

Possibly related, the following SQL shows which objects in the database had QUOTED_IDENTIFIER ON at compile time (ie. which will be interpreted with that value when used). From this I see that triggers are also affected by this setting. Perhaps the Agent job update is invoking a trigger which is somehow related? I do note, however, that all objects are listed as uses_quoted_identifier = 1. In other words, if the resolution was to re-create objects while setting QUOTED_IDENTIFIER ON prior to re-creating them, I don't see what difference it would make; all objects already seem to have been created that way.

SELECT uses_ansi_nulls, uses_quoted_identifier, name, *
 FROM sys.sql_modules -- WHERE object_id = object_id('SampleProcedure')
 left outer join sys.sysobjects on sql_modules.object_id = sysobjects.id


texts:
I have an established service broker relationship between two databases in our production environment. We do a weekly restore of these databases to another server for the purposes of testing our restore ability, but it has the added benefit of being able to mess around on production databases that aren't in production.

To make a long story short, I am able to get service broker running after the restore and can get messages to flow between the two. Mostly. For one particular message type, I'm getting "This message could not be delivered because it is a duplicate." in the error logs.

From the research that I've done, this typically indicates a routing issue at the target. However, that doesn't explain why other message types work as advertised. Additionally, I've checked the routing by hand and everything appears to be okay. Any thoughts appreciated.


texts:
I have the following table:

+------------+--------+------+-----+---------+----------------+
| Field      | Type   | Null | Key | Default | Extra          |
+------------+--------+------+-----+---------+----------------+
| id         | double | NO   | PRI | NULL    | auto_increment |
| az1        | float  | YES  |     | 989898  |                |
| el1        | float  | YES  |     | 989898  |                |
| az2        | float  | YES  |     | 989898  |                |
| el2        | float  | YES  |     | 989898  |                |
| rfs_angle  | float  | YES  |     | 989898  |                |
| scan_speed | float  | YES  |     | 989898  |                |
+------------+--------+------+-----+---------+----------------+

I inserted this data more than 100times

INSERT INTO lidar_traj_table (az1, el1, az2, el2, rfs_angle, scan_speed)  VALUES (49.100, 0.000,989898,8.000,1.0,1.000)

When I'm trying to select the data I'm receiving nothing:

SELECT * FROM lidar_traj_table WHERE az1=49.1

When I'm selecting smaller data then 49.1 than I get all the rows

SELECT * FROM lidar_traj_table WHERE az1<=49.1

Why am I getting empty result for this:

SELECT * FROM lidar_traj_table WHERE az1=49.1


texts: Why MySQL Workbench automatically sets NOT NULL for ID column that is PRIMARY KEY?

texts: My question is why MySQL Workbench automatically sets NOT NULL for ID column that is PRIMARY KEY? I thought that PRIMARY KEY's can't be NOT NULL by definition? Is it really necessary to set it to NOT NULL?


texts: Select most recent inspection, break tie between inspections from same year

texts:
I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_date date,
    length number(10,2)
);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 1, 100, to_date('01/JAN/2013 6:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 2, 100, to_date('01/JAN/2014 7:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 3, 100, to_date('01/JAN/2015 8:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 4, 100, to_date('01/JAN/2016 9:00:00AM','DD/MON/YY HH:MI:SSAM'), 750);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 5, 100, to_date('01/FEB/2016 10:00:00AM','DD/MON/YY HH:MI:SSAM'), 250);

commit;

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from road_insp;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         1        100 01/JAN/13 06:00:00AM       1000
         2        100 01/JAN/14 07:00:00AM       1000
         3        100 01/JAN/15 08:00:00AM       1000

         4        100 01/JAN/16 09:00:00AM        750
         5        100 01/FEB/16 10:00:00AM        250

I'm able to select the most recent inspection of each road_id using this query:

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from
(
      select
        r.insp_id
        ,r.road_id
        ,r.insp_date
        ,r.length
        ,row_number() over (partition by road_id order by insp_date desc) rn
    from user.road_insp r
)
where
     rn =1
;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         5        100 01/FEB/16 10:00:00AM        250

However, rather than simply just select the most recent inspection, I want to do do something a bit more complex:

Get the most recent inspection
But if there is more than one inspection in that inspection year, break the tie by getting the longest inspection

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         4        100 01/JAN/16 09:00:00AM        750

In other words, if there are multiple inspections in the latest inspection year, it doesn't really matter which one came first (within the context of the year). It's more important which inspection was longest.

How can I do this?


texts: Database Backup - In Process - Power Outage

texts:
I am using SQL Server 2008 R2.
What happens if a database backup (Full/Differential/Transaction Log) is in process and there is a sudden power outage or the system crashes? Does that corrupt the backup set?

If so, then what are the available options so that if and when a database restore needs to be done I don't end up with a corrupted backup set.

If not, then what will be the status of the backup that was in process?


texts: What is the status of the PostQUEL source code?

texts:
Recently as part of my answer to "Is the keyword “ALIAS” actually used?" I wanted to go back further than Postgres95. Postgres95 is the first version of Postgres that was migrated to use SQL rather than QUEL.

Is POSTQUEL (the version that used QUEL) open source and is it available anywhere? The PostgreSQL repo starts with an import of Postgres95.


texts: Skipping some data from Indexing in SQL Server

texts:
I have a situation where SQL Server is giving error that index size exceeding 900 bytes. Its happening because one of the column in index definition is varchar(2000), and whenever its length goes over 800 it gives the error.

So, my question is,  is there any way to say in index definition to include only first 800 characters from the column?

Some people on blogs have suggested to add one additional column to the table which will have first 800 characters of other column, and add indexing on this new additional column rather than original column. But I think that will impact the overall performance because read queries are still querying on original column.

Please suggest if there is some other way to tackle this problem.


texts: what is so special about MongoDB indexing?

texts: I was reading the MongoDB documentation, where it lists Indexing as an special feature. But after reading the doc, I still can't see what it is that makes MongoDB index special. Anyone can explain?


texts: Which is the best approach to "suspend" the activity of a whole AOG cluster thinking on start it again later?

texts:
My company is going to migrate some services from one on-prem site to another but the database servers will remain in the same location (for now).
I've been asked for "suspend" all the activity of the three AOG cluster nodes (all secondary instances are readable without routing), during a long maintenance window.
Two main options are coming to my mind:

Maybe not the fanciest. Suspend data movement and then stop SQL Engine service at the secondary replicas first and then stop the primary at the end. When the time to start services comes, start the primary first and the secondaries later in inverse order and resume data movement at the end.
Disable jobs and intended logins at all instances to enable them again later keeping all AOG activity running but without user access.

I know the "best option" is completely relative to the context, but I would like to read some opinions.
Thank you,


texts: SQL Server - why are window functions not permitted in update statements?

texts:
When running an update statement, such as the one below, I get an error telling me that

  Windowed functions can only appear in the SELECT or ORDER BY clauses.

UPDATE dbo.Dim_Chart_of_Account
SET Account_Order = LAG([Account_Order]) OVER (ORDER BY [Account_SKey])

I know that this can be easily worked around using an updatable cte, like below

 WITH my_cte AS (
     SELECT [Account_Order], LAG([Account_Order]) OVER (ORDER BY [Account_SKey]) AS acc_order_lag
     FROM Dim_Chart_of_Account
)
UPDATE my_cte
SET [Account_Order] = acc_order_lag

My question is, are there any reasons why this is not permitted in an update statement, should I avoid using an updatable cte as a workaround?

My concern is that there are issues when using window functions with update statements and therefore I'd like to understand if this is an acceptable method or should be avoided.


texts: Are view references in a query properly called "derived tables" as such?

texts:
While answering a question on stackoverflow, I presented a definition of derived tables:

  A derived table is a complete query, inside of parentheses, that is used as if it were a real table.

But a commenter objected:

  Though there are other kinds of derived tables besides those "inside of parentheses". ... [such as] Views and Table-Valued Functions ... .

and further backed this up with:

  From the ISO/IEC 2003 Spec, section 4.3 of the Framework volume, page 13 of the August 2003 draft Spec: "An operation that references zero or more base tables and returns a table is called a query. The result of a query is called a derived table." Note that both Views and Table-Valued Functions return "the result of a query", which is a derived table. Microsoft (and to a lesser extent, Oracle) are notorious for mistakenly equating "derived table" and "sub-query" in their documentation, but Derived Tables do also include pre-defined queries like Views.

So what's the real scoop, here? Am I to relegate what I thought of as derived tables to simply "FROM clause aliased inline subqueries" or are views not properly derived tables?

Please note: I searched for quite a while online and could not find anything definitive. I don't have a copy of the said spec.

Also, I think it's worth addressing something else. Let's say that views are properly called "derived tables". Does this make the reference to the view also a "derived table" or merely a reference? For an example with a CTE that should drive the point home:

WITH SalesTotals AS (
   SELECT
      O.CustomerID,
      SalesTotal = Sum(OrderTotal)
   FROM
      dbo.CustomerOrder O
   GROUP BY
      O.CustomerID
)
SELECT
   C.Name,
   S.SalesTotal
FROM
   dbo.Customer C
   INNER JOIN SalesTotals S
      ON C.CustomerID = S.CustomerID;

The SalesTotals CTE introduced with WITH is a derived table. But is INNER JOIN SalesTotals also a derived table, or just a reference to a derived table? Does this query have two derived tables or one? If one, then by extension I think that a view may be a derived table, but referencing it may not have to be a derived table.


texts: MariaDB multi-master replication (star topology) - Bizarre out-of-sync behavior

texts:
This may look familiar, I've been learning about this stuff for a few days - but this is a new question. I have a setup like this:

Every node is a master, and I have them all replicating to each other through ssh tunnels, and everything is grand - until I test one the scenarios we will have to deal with: loss of connectivity on a node.
Steps to reproduce...

Sever connectivity between hub (cloud) and one of the local servers.
Update a row on the the hub server.
Update the same row on the local server (non-PK column, same PK record).
Restore connectivity.

Result: The hub, and all of the other local servers, end up with the updated value from the local server that was disconnected. The local server where the updates were made end up with the value set on the hub server.
From the docs on Global Transaction ID:

However, if user updates are done independently on multiple servers at the same time, then in general it is not possible for binlog order to be identical across all servers. This can happen when using multi-source replication, with multi-master ring topologies, or just if manual updates are done on a slave that is replicating from active master. If the binlog order is different on the new master from the order on the old master, then it is not sufficient for the slave to keep track of a single GTID to completely record the current state.
The domain ID, the first component of the GTID, is used to handle this.

I do have unique domain ids set on each of my servers. Then I found this paragraph on the same page which contains the following:

Using master_use_gtid=current_pos is probably easiest, as there is then no need to consider whether a server was a master or a slave prior to using CHANGE MASTER. But care must be taken not to inject extra transactions into the binlog on the slave server that are not intended to be replicated to other servers. If such an extra transaction is the most recent when the slave starts, it will be used as the starting point of replication. This will probably fail because that transaction is not present on the master. To avoid local changes on a slave server to go into the binlog, set @@sql_log_bin to 0.
If it is undesirable that changes to the binlog on the slave affects the GTID replication position, then master_use_gtid=slave_pos should be used. Then the slave will always connect to the master at the position of the last replicated GTID. This may avoid some surprises for users that expect behaviour consistent with traditional replication, where the replication position is never changed by local changes done on a server."

So, I tried setting the master_use_gtid to slave_pos, but I am still getting the same behavior. Previously it had been set to current_pos. I'm kind of at a dead end now.
I am not quite sure exactly what I expected as far as which update would win, but I do know I expected the servers to end up with the same data after connection and syncing.
The question is: How can I get all nodes to always end up with the same data, and is there a way to decide which value wins?


texts: Percentage difference of the last value from the previous values of a column based on certain data group within the same table

texts:
I have a table shown in below image. Actually i want to calculate the percentage difference in this table. The methodology of calculating the difference will be as following.

1-Records are logically grouped by Column2.

2-We want to compare value of column3 with the other records of the group but the column3 value is taken based on the max-value of column 1 in the group.


texts: Replace function removing Ones / 1 when removing char(185) (superscript 1)

texts:
Ok as the title suggests this is occuring.

When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well.

SQL SERVER 2008R2
UNICODE Data

DB collation - collate SQL_Latin1_General_CP1_CS_AS
Tempdb has default

Any other details I could provide which might be helpful, let me know.


texts: Can I write a Select query to update the data too

texts: Can I write a Select query under a jdbc connection ,having a resultset object and which would update the records on just passing the select query them in my sql.


texts: How to determine if an Oracle table is locked or not?

texts:
We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2.

Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?


texts:
Why can't I get this to cast as a DT_STR - the data source is WSTR, but shouldn't this cast make the output DT_STR?

(IPUNSPSC) =="**no_UNSPSC_available**" ? (DT_STR,20,1252)NULL(DT_STR,20,1252) : (DT_STR,20,1252)TRIM(IPUNSPSC)


texts: SSRS Error : Could not connect to server, ( since the TLS 1.0 was disabled)

texts:
Since the recent patch where TLS 1.0 was disabled and 1.1, 1.2 Enabled , we are having issues where the SSRS in the server cannot make connections to the Database server.

We had to re-enable TLS1.0 to make the connection successful. Is there something that can avoid enabling it as it was identified to be a vulnerability.
What needs to be done to make it work with TLS 1.2?


texts: Suitable schema to record email sent and response received and invoicing in DB

texts:
When a form submission is made on a website:

An email with form's details is sent to a set of recipients email addresses stored in a DB.
The email contains a link for the recipient to show interest. When the link is clicked a response is sent to server with verify_code. The server will UPDATE the table below setting has_confirmed to true or 1 to record their interest on DB.
An email is sent back to the recipient as acknowledgement

This is the current table to record the above:

RequestSentConfirmation

Sent_ID (int) PK
Request_Id (int) FK
Recipient_Id (int) FK
verfied_code (varchar(100))
has_confirmed (bool)
when_confirmed (datetime)

Request_Id is ID to form submission details stored in another table.
Recipient_Id is ID to recipient email addresses stored in another table.

Is it better to store the has_confirmed in the same table as above.

The confirmations are required to collect and generate an invoice for each recipient.
To generate an invoice from above table I will need to run SQL script to check which sent emails are confirmed.

Or best to store the confirmations in separate table? That way I could run reporting script on the separate confirmation table to generate invoice for each recipient?

Is there a better way to design this with DB since I'm using emailing here.


texts: Password hashes: Fixed-length binary fields or single string field?

texts:
I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are:

Storing the hash and the salt together as a string, delimited by some agreed character (for example: "E69B4A103...598D$59FBA6")
Storing the hash and the salt in separate fixed-length binary fields

Obvious advantage to the first is only having to retrieve one field, but at the cost of the field being significantly larger (especially when using SHA512 or a large salt), and performance issued with string manipulation later down the road in the application.

Option two has the advantage of being much smaller, but at the cost of a slightly more complex query to retrieve two fields, not one.

Our question is: which option would make the most sense for a large database where performance is a key factor?


texts: Should DBAs also be acting as Security Administrators?

texts:
One standard I have been tasked to bring my databases up to asks me to separate security functionality and non-security functionality. Based on supplemental reading, this seems to be talking about hardware, software, and firmware dealing with encryption, authentication, authorizations and auditing.

Pursuant to this goal, and just as a general question to you experienced people, is it acceptable for a DBA to also be acting as a Security Administrator? Separation of duties would seem to say no, as DBA work, while administrative and highly privileged in nature, is mostly not security-related in nature. But if it would be prohibitive to hire someone as a DBA and another as a security administrator, what would be the largest areas of concern, if any, if one person performed both of these sets of duties?

ETA: I can't give many details to the best of my understanding of my restrictions, but security concerns are high, budget is tight, and the databases range from small, non-application databases with few users to users in the thousands with an expansive number of tables, each with an expansive number of rows of information. DBAs are utilizing the DBA role, so the most expansive set of privileges. And it looks like I accidentally removed the oracle tag, so I'll put that back on and mention that this is an Oracle database.

For the sake of discussion, let's focus on the largest subset of databases. These have personal and financial information in them. I think I can say that we are shooting for a standard that heavily leans on the NIST database standard, but isn't NIST for whatever reason. Not sure if that helps at all.


texts: Best way to proceed, running XP_CMDSHELL/xp_create_subdir without sysAdmin

texts:
After reading a lot about this issue, I know that if I create a proxy account, I can do what I want. I just need some enlightenment to see if there's a better way to do what I want.

We have a procedure ( a backup procedure ) inside a database, that create subfolders. So the user can backup the database, run his work, and then backup it again. So We have 2 simple backups. if there's some problems, they tell us, and we ( dbas ) restore the database with the "before" backup.

Well, this is the procedure  ( without the SETS and DECLARES ):

    Exec xp_create_subdir @Diretorio

    Backup Database @NomeBaseDadosSemColchetes
        To Disk = @NomeCompleto
               With --Backup Set Options
                    Compression,
                    Name = @NomeBaseDadosSemColchetes,

                    --Media Set Options
                    Init,
                    Skip,
                    NoFormat,

                    --Error Management Options
                    CheckSum,
                    Stop_On_Error,

                    --Monitoring Options
                    Stats = 10,

                    --Tape Options
                    NoRewind,
                    NoUnload

Problem 1) To run this, I need a login that is SYSADMIN, because of the xp_create_subdir.

I read that If a create a Proxy account, I can create a windows login, configure it to run without sysadmin, and then configure it to be a proxy account.

The only way to do this ( Proxy account ) is using a Windows account?

I notice this problem because I found out that developers are using this SysAdmin account to do stuff ( nothing wrong, but putting databases offline, dropping their databases without noticing me, and etc )

So, how can I drop the SysAdmin permission for that login ( it only uses sysadmin because of this xp_create_subdir. or is there a better way to do this?

And, the DEVS know the password for this login, because we need to pass it inside their software.


texts: Why COALESCE within a subquery returns NULL?


texts: Where and how to store my weird datamart

texts:
I could really use some help here.

Here's my situation.

I'm building a datamart in SQL Server 2005, which will furnish a single report (for now).  We have 26 clients (medical organizations) that will be using this report. Each client will have between 1 and 2,000 users accessing it at any given time (most likely to average in the 50's but it needs to scale). There will be 26 versions of the same rdl, one for each client.  Each one will access its own respective client database.

The interface for said report is SSRS 2012.  This is our first 2012 report - the rest are still 2005, and the rdl will access a 2005 database.  I am not using OLAP; the SSRS report runs SPs and views.

We built a virtual server and installed SQL 2012 to host the report (both these virtual servers live on the same physical machine, in case that matters). Nothing else will be running on the SQL 2012 virtual server.

These are the facts about the environment...

Our system is not OLTP heavy - with one exception, which I'll describe below, it's all Read (except for the ETL of course).

We have one client-facing database per client, 26 altogether. In these we store transactional data, rolled-up data, some report-ready flat tables and a ton of T-SQL code that crunches numbers when clients pull up reports in SSRS.  I'll refer to these databases as "operational" because, for me, they will essentially function as ODS's.

The operational databases are loaded by a horrific ETL process (clients have varying load schedules - usually monthly or weekly).  I'll be building a mini ETL process (hopefully not so horrific) to populate the datamart from those operational databases.

All of the datamart's dimensions are conforming, but due to HIPAA restrictions, some of them (like Physician and Patient) can't be stored in a central database, and neither can the fact table. So there will need to be 26 versions of the same fact and dimension tables, one for each client.

There is a real-time component to our system. Physicians and nurses can enter transactional data via our site and all reports need to reflect the changes immediately. As for the datamart, they'll only affect the fact table. For what it's worth, this is why I decided not to use SSAS. I know diffs process really quickly but it just feels like too many moving parts.

I plan to create a trickle-in fact table with a view that combines it with the main fact table.  Again, I'll need 26 of these.  A pared down version of my new ETL process will need to run upon each user edit.

Here are my questions...

Where should I store the 26 sets of datamart tables?

On a dedicated 2005 server, away from the SSRS server and the operational databases?
On the same server as the operational databases but in dedicated dds databases?
Within the operational databases themselves?
On the SQL 2012 reporting server?
On the moon?  Other?

Where should I store the 26 trickle-in fact tables?

In the same database as the main fact tables?
On the same server as the operational databases but in dedicated DDS databases?
Within the operational databases themselves?  This seems logical to me given that they'll need to be unioned at runtime...

Should I create a central database for non-sensitive dimensions?

Maybe create a cloning process to copy them to the individual DDS's?
Or is it simpler to just have 26 of these darned things as well?

With all of these questions, I’m concerned about good design practice, but mostly about the performance of the report and the performance of the ETL that will need to run upon user edit.

I hope all of this made sense.  I would very much appreciate any feedback!

EDIT: @Jon Seigel - There will be 26 versions of the same rdl, one for each client.  Each one will access its own respective client database.

EDIT: @JNK - I merged accounts and read the faq's. Hopefully I'm responding correctly now.


texts: Does lightweight pooling disable built-in CLR facilities?

texts:
Running SQL Server in fibre mode (lightweight pooling) disables the use of SQL CLR:

  Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling". Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management.

On the other hand, disabling SQL CLR alone (without enabling lightweight pooling) does not disable the built-in CLR types like geometry, and geography (though hierarchyid is mentioned above), as shown in How can "HierarchyID" type work when "CLR" is disabled?

Now some new language features rely on the CLR, for example the FORMAT function:

  FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).

Does running SQL Server in fibre mode disable the FORMAT function and/or using the CLR types?


texts:
Does Workspace memory (part of buffer pool) is limited for only Hash Join, Sort etc. only?

If I have a query that does use a nested loop join (wihtout a sort or hash), would it not use workspace memory?

Where would worktable for a simple query that return 100 rows will be created? in Tempdb..


texts: MySQL 100+ instances on one Windows server

texts:
Please don't ask why I need 100+ instances on one server because I cannot say; suffice to say that I'm not impressed.  They will be very small databases and not definitely not under any great load (one or two users per instance).

The server is Windows 2012R2 64bit with 8GB RAM.
The MySQL version is 5.6.26.
innodb_buffer_pool_size=415M

After 27 instances are running I get the error in the Windows event log for every instance after that:

  InnoDB: Cannot allocate memory for the buffer pool"

The instances will have the most basic of databases, probably no more than 10 records in each and 10 - 15 entries in each record.

If needs be, I can allocate more memory.