texts: Using Merge in Oracle to update a table and clear out a field if the record is not in the source table

texts:
I need some help converting an MSSQL Merge statement into Oracle

MERGE INTO A
      USING B on a.a_id = b.b_id
WHEN MATCHED and isNull(a.a_field, '') <> isNull(b.b_field,'') THEN
  UPDATE SET a.a_field = b.b_field
WHEN NOT MATCHED BY TARGET THEN
  INSERT (a_id, a_field)
  VALUES (b.b_id, b.b_field)
WHEN NOT MATCHED BY SOURCE
  UPDATE SET a.a_field = ''

I've been able to get the conversion to this point

MERGE INTO A
      USING B on a.a_id = b.b_id
WHEN MATCHED THEN
  UPDATE SET a.a_field = b.b_field
  WHERE nvl(a.a_field, '') <> nvl(b.b_field,'')
WHEN NOT MATCHED THEN
  INSERT (a.a_id, a.a_field)
  VALUES (b.b_id, b.b_field)

I don't know how to setup the merge statement so that it will clear out a.a_field when there is no record for that same a.a_id in the B table.


texts:
I'm having a hard time trying to get PostgreSQL to use an index that I created. I understand that PostgreSQL is smart enough to determine when an index should be used because in most cases a seq scan is fine. However, I want to make my table to run faster. Can someone tell me what am I doing wrong with my index? My Query Plan time and cost does not change.

I know when I use the index in Oracle, it actually improves the performance. I'm not sure how I can get PostgreSQL to do the same.

This is my query:

select sum(lo_revenue), d_year, p_brand1
from lineorder, dwdate, part, supplier
where lo_orderdate = d_datekey
    and lo_partkey = p_partkey
    and lo_suppkey = s_suppkey
    and p_brand1 between 'MFGR#2221' and 'MFGR#2228'
    and s_region = 'ASIA'
    group by d_year, p_brand1
    order by d_year, p_brand1;

This is my index:

create index index1 on lineorder(lo_revenue,lo_orderdate,lo_partkey,lo_suppkey);

Here is what I get when I run an EXPLAIN:

QUERY PLAN

-------------------------------------------------------------------------------------------------------
------------------------------
 Sort  (cost=166357.23..166357.26 rows=13 width=17)
   Sort Key: dwdate.d_year, part.p_brand1
   ->  HashAggregate  (cost=166356.86..166356.99 rows=13 width=17)
         ->  Hash Join  (cost=6023.57..166338.87 rows=2398 width=17)
               Hash Cond: (lineorder.lo_orderdate = dwdate.d_datekey)
               ->  Hash Join  (cost=5928.06..166207.39 rows=2398 width=17)
                     Hash Cond: (lineorder.lo_suppkey = supplier.s_suppkey)
                     ->  Hash Join  (cost=5869.45..166084.74 rows=10682 width=21)
                           Hash Cond: (lineorder.lo_partkey = part.p_partkey)
                           ->  Seq Scan on lineorder  (cost=0.00..137603.98 rows=6001198 width=16)
                           ->  Hash  (cost=5865.00..5865.00 rows=356 width=13)
                                 ->  Seq Scan on part  (cost=0.00..5865.00 rows=356 width=13)
                                       Filter: (((p_brand1)::text >= 'MFGR#2221'::text) AND ((p_brand1)
::text <= 'MFGR#2228'::text))
                     ->  Hash  (cost=53.00..53.00 rows=449 width=4)
                           ->  Seq Scan on supplier  (cost=0.00..53.00 rows=449 width=4)
                                 Filter: ((s_region)::text = 'ASIA'::text)
               ->  Hash  (cost=63.56..63.56 rows=2556 width=8)
                     ->  Seq Scan on dwdate  (cost=0.00..63.56 rows=2556 width=8)
(18 rows)

Time: 0.821 ms


texts: Mysql setting a record as deleted or archive

texts: Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?


texts: Can't seem to understand why STUFF isn't working

texts:
I've got two tables that store questions and possible answers.
The questions table can have multiple possible answers to it.

I want to display the possible answers it can have per question..
For instance lets say I have two questions

1) Hows the weather?
2) Do you love MI?

And the other table I have possible answers

1) Good
1) Fair
2) Yes
2) No

My expecting result would be

1 Good, Fair
2 Yes, No

But instead I get:

1 Good, Fair, Yes, No
2 Yes, No, Good, Fair

Here's what I am doing...

SELECT x.SurveyQuestionID, PossibleAnswers = STUFF((
    SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa
    INNER JOIN SurveyQuestion sq
    ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID

I'm getting the right number of rows due to the group by, but the possible answers seems to be giving me every possible answer for each question.


texts: Unique time range or database schema for a scheduling system

texts:
I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap.

In my SheduledSlot table I have:

id    integer PRIMARY KEY
begin datetime(or integer for SQLite)
end   datetime(or integer for SQLite unixtime)
media_item integer (foreign key)

How do I enforce a constraint which ensures a unique time range for each scheduled item?


texts:
I haven't tried using MySQL transaction before, I just want to clarify something.

If two users execute a query at the very exact time, how MySQL would handle this?
e.g. the users is trying to update a record.

user1: update table set column = column - 4 where column_id = 1;

user2: update table set column = column - 7 where column_id = 1;

Now if I use transactions, will MySQL choose which query will be executed first and lock the second user until the first query is committed? Will that be a table lock or a row lock?

What if a third user will issue a select statement? What will be the value MySQL will return?

P.S. this will be on Innodb.


texts: Oracle DBMS Scheduler Email Notifications Dynamically Send Server and Instance Name

texts:
We have two Oracle database servers. One for production with one instance on it, and another one with multiple instances for Dev, Test, etc. I have some jobs that run on production and email me when they fail. I had originally set the sender to be something like server1.prod@company.com, so that I knew what environment the error was coming from.

Problem is when we copy production to one of the test or dev environments to get them up to date, then the job errors out for whatever reason, but it says it is coming from server1.prod@company.com which is incorrect.

I would like the notification email to pick up the servername and instance name that it is sent from, so if it comes from test it would be server2.test@company.com. If that is not possible, then it would be fine to get this info in the subject line or body of the email.

I can get the server and instance name pretty easy with

SELECT  host_name, instance_name
FROM    v$instance;

,but I can't figure out how to get this to be used in the job email notifications.

I set these jobs up using the sql developer wizard, so if you can show me how to change it with that would be fine, or with pl/sql code, I am ok with either.

If none of this is possible, then is there I way I can make the jobs to not send out once they get to the non production environments at least because I really don't care if they fail there.


texts: Any possible way to encrypt SQL Server 2012 database or backup?

texts: We don't have SQL Server 2014 or later versions, so I can't take advantage of the backup encryption enhancement available in those. But I would like to know if there are any ways to use any type of encryption for a SQL Server 2012 databases. It could be encrypting the database, backup, or any other object. Any help is appreciated along with the scripts to do so if available.


texts: What are best practices for storing many iterations of a product in an RDBMS?

texts:
We are building a tool to track the prices of products over time, and using Postgres as our RDBMS. It is important that product attributes can be changed, and that the history of an product's attributes be preserved forever. Here is a schema we designed based on OpenStreetMap's internal schema:

We have a 'products' table on the left storing every version of every product, and a 'current_products' table on the right storing only the most recent version of each product. Every time we want to change a store, we:

create an entry in changesets
read the latest entry of the product in 'products', increment version by one, and create another entry with the changes
delete the corresponding entry in 'current_products' and create a new one with the changes and the latest version number from 'products'

We want to enforce as many business rules in the database engine as possible rather than relying on our software to keep things consistent, and this schema feels pretty "off", so we welcome any suggestions. Thanks in advance!

Edit:
Revised the schema based a response from @bbaird . Also decided to include versioning of stores and users. Tied products, stores, and users together with price table.


texts: SQL Server 2016 Powershell: Restore-SqlDatabase with -ReplaceDatabase and -RelocateFile

texts:
We have a several-node SQL Server 2016 AlwaysOn cluster, and have a need to move databases from one node/Availability Group to another quite often, so I'm trying to automate the process.

To add database to secondary replica, I need to prepare it first, i.e. backup primary, create new one on secondary, restore backup to secondary. The problem is that often the paths (local drives) do not match, for instance:

PRIMARY: D:\Databases\db.mdf, D:\Logs\db_log.ldf
SECONDARY: E:\Databases\db.mdf, E:\Logs\db_log.ldf

I'm trying to write a PoSh script, assuming that I have to create new database on SECONDARY server first, and came up with the following:

$SQLQuery = "CREATE DATABASE [$Database] ON PRIMARY ( NAME = N'$Database', FILENAME = N'$DestinationDatabaseFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ) LOG ON ( NAME = N'$LogName', FILENAME = N'$DestinationLogFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB )"
Invoke-Sqlcmd2 -ServerInstance $DestinationServer -Database "master" -Query $SQLQuery

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)

Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)

As far as I understand on restore I need to relocate the files ( -RelocateFile ), and also replace the database ( -ReplaceDatabase ). The error I'm getting is:

  Restore-SqlDatabase : System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'db' database.

Servers are of the same version, databases of same compatibility levels.

Perhaps somebody could suggest a way around?

Thanks!


texts:
How do I execute a Subquery once in a Query like this

http://sqlfiddle.com/#!9/8b149d/1/0

select
id,
(
  select group_concat(user)
  from table_b
) as subquery
from table_a

to maintain performance of the Query, Or it is better If I run two Queries separatly?

I want a result as if I used

select group_concat(user) from talbe_b;
select id from table_a;

but in a single query.


texts:
I have a query that hits multiple tables in an Oracle database (11g). I don't have access to the server itself. I use SQL Developer or SQL*Plus to connect.

There are about 31 million rows, and 7 columns, and I want to dump it into a CSV file.

How can I achieve this?


texts: Concatenating Strings & Using FORMAT Together - NOT WORKING

texts:
I'm literally pulling my hair out trying to use the FORMAT function to format a date into a simple string.  Please someone pull the pin and tell me what stupid thing I'm missing!

Works:

SELECT TOP 10 FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Works:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Does NOT Work:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10) FROM REV ORDER BY REV_ID DESC

I can't figure out how to simply concat a string onto the end of the output of FORMAT.


texts:
I have my own app that get low traffic (like 100 ppl per day).

As part of my research I am 'logging' all the queries done to by the server in a table in the database. An example, if user enters home page it will register two queries for getting the data to display.

According to Google Analytics by average an user enters 7 pages per session, and in average I have 130 sessions per day. Assuming a bad design and think that in average a page does three queries would be a total 2730 queries per day.

Right now, my research does not have a deadline or finish point soon in the future and I am scare that at the end, all the data will be too much too process and study.

Facts:

Right now in six months of working, SELECT COUNT(*), Throws 1.360.000
rows and weights 5.56 GB.
I am a master student of computer science with emphasis of user behavior.
The server runs in a really low memory pc and there is not budget in the moment to changed it.
I cannot change the code as it could change the results.
I do not know anything about maintenance of a DB in a server and I just simply doing a service restart everyday.

The questions:

Is it possible to overload the server of data considering the poor excuse of pc that I called server?
Is it stupid to think to store the log in a way that every month will store it in a file and delete those rows from DB?
Am I right to be worried about the size of the database?


texts: How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows?

texts:
I've got a sql file TW_popul.sql containing multiple INSERT statement:

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32353357, 8.67, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29639547);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32358776, 1.84, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29643922);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374786, 11.26, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656743);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374911, 1.49, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656849);

Some of those insert statements a breaking constraint rules and throwing errors(ex. "ERROR at line 1: ORA-00001: unique constraint (TW.BT_BANK_TRANSACTION_ID_PK) violated" ).

1 row created.

1 row created.

1 row created.

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
*
ERROR at line 1:
ORA-00001: unique constraint (TW.CF_FEE_ID_PK) violated

1 row created.

1 row created.

How do I collect all the rows generating an error (in an Error_table for example)?

I've read about TRY... CATCH but i'm not sure it applies to catching multiple errors.


texts: One to Many Relationship: Describing the Tables

texts:
If we have two tables in a One-to-Many relation ship, is there a standard name to properly refer to the tables?

For example if I have a table of books, and a table of authors (ignoring for now the possibility of joint authorship), how can we generically refer to the two tables in the relationship.

I have come to refer to the authors as the Parent table, and the books as the Child table, arguing that the books may be regarded as the brain-children of the authors. This description doesn’t work as well for, say, a table of sales and saleitems.

I have sometimes seen them referred to as the one and many tables, but I wonder whether there is a more useful way of referring to them.

Is there a standard way of referring to the tables?


texts: DB2: The transaction log for the database is full, But there is no 'First active log file'

texts:
First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem.

This is a DB2 database, and our testers are saying that they get:

  The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=4.13.127 SQL Code: -964, SQL State: 57011

I did increase the number of LOGPRIMARY to '40'. However, it appears that the transaction logs are still full.  I then decided to 'prune' the transaction logs. The procedure I have is to Prune all prior to the active transaction log. However, I cannot determine the active log:

$ db2 get db config for MyDB | grep -i 'log'
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 278
 Log buffer size (4KB)                        (LOGBUFSZ) = 1997
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 40
 Number of secondary log files               (LOGSECOND) = 12
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = 0
 HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
 First log archive method                 (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO
$

I do note that the transaction log files do appear in /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/.

Is transaction logging correctly setup ?  Would increasing the logs be the answer?  Or should I push back on the users to encourage them to do more commits?


texts: Select into query, insert fails, but the table is created

texts:
I am using SQL Server 2016 and I tried to the following query.

SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp

This query is obviously in error. Because it does not convert. However, the temporary table (#tmp) is created even if the query fails.

Why? I think this is by design, but I want to know why.

PDW (parallel datawarehouse) does not create temporary table.


texts: Party Data Model / Universal Data Model Example

texts:
I am trying to implement Party Data Model / Universal Data Model in our application which needs to store different relationships between entities (People, Companies).

Example of relationships: Employer/Employee, Husband/Wife, Seller/Customer.

We also need to store the different types of roles a person can have inside a company.

For example, a person can be Employee at Google, but at the same time occupies a Head of Development position.

I am using this schema as an example, but i need to admit that i don't fully understand it.

From the schema, i think that party role is a tree-structure table? A role can have a parent role? Example: CEO > Head of Development > Developer

Or, party roles are organized in categories, like in the picture? Person Role, Customer, Organization Role.

I have tried to create the DB schema for this picture, and this is what i have:

And the data would look like this

Party
John Smith (Person)
Google (Organization)
Jane Smith (Person)

PartyRelationshipType
Employment
Married

PartyRole
Employer
Employee
Husband
Wife

PartyRelationship
Id          RelationshipType            FromParty           ToParty         Role            FromDate            ThruDate
1           Employment                  Google              John Smith      Employer        1/01/2008           16/08/2011
2           Married                     Jane Smith          John Smith      Wife            15/11/2000

Is the structure ok?

How can i define the relationships between Google employess for example? All of them they are employees, but at the same time they occupy different titles. Should i create a new Role for each title? or this data should be saved somewhere else?

Can somebody show me a real-data example?


texts: CreatingDC not updating after Domain controller Migration

texts:
Recently we have switched our domain controller and during the migration I have noticed on the cluster manager the below error

Cluster network name resource 'Cluster Name' encountered an error enabling the network name on this node. The reason for the failure was: 'Check if a domain controller is reachable and that the computer object is present in Active Directory'.

In our environment we have always on availability group. I am able to move resources but did not try to failover .
When I looked into the registry settings HKEY_LOCAL-MACHINE - Cluster - Resources I have noticed the CREATINGDC still showing the old Domain controller. Please advise whether I need to update it to the new domain controller or I can leave as is.  Any help will be greatly appreciated  as I fear failover will not work.


texts: PhpMyAdmin Import Error-MySQL server has gone away/Unrecognized Keyword

texts:
So I've exported a MySQL database from PHPMyAdmin (which I access from within IIS Express on a Windows 8 machine) and I'm trying to import this database onto another machine, however on that machine I receive the following errors:

Please help. I need to be able to restore this database and I have no idea how to fix this.

I tried going into the sql file and changing the foreign key line in the sql file (it was set to 0), but that didn't make a difference. Where is it set to ON? I can't figure that out.


texts: Memory usage grows on SQL Server Analysis Services 2012 until cubes fail to process

texts:
We have a 64-bit SQL Server 2012 SP4 Enterprise Edition instance with Analysis Services (Analysis Services is running on the same VM as the SQL Server database engine) where the memory usage of the Analysis Services process continues to grow until the point where cube processing begins to fail with a report of "The operation has been cancelled because there is not enough memory available for the application". Once this happens our only option appears to be to restart the Analysis Services process and begin the cycle again.

I have experimented with changing the low/total/hard memory limits in Analysis Services, but so far have not found anything that works for more than about 5-6 days.

What I have found is that the performance counter for "Cleaner Memory nonshinkable KB" continues to grow day to day, which is not something that I would expect.

Would anyone have any insights or suggestions to help diagnose this issue?

Some addition information:

The VM has 20 GB of RAM allocated to it.
The SQL Server is currently
set to limit memory usage to 12 GB.
The Analysis Services
low/total/hard memory limits are currently set to 40/50/70 (percent).
The SQL Server has about 20 databases of moderate size totaling no
more than 100 GB.
There are five Analysis Services databases / cubes.
We use multidimensional models.
The cubes are fully processed overnight on a daily basis.
Proactive caching is used to keep the cubes updated during the day.
Web sites access the cubes via the OLAP "pump" (msmdpump.dll).
This VM was recently set up to replace a physical server that had a very similar
configuration except for having a few more GB of RAM and a less
recent service pack of SQL Server. That physical server had been
running for years and we have never experienced this issue with it.
Unfortunately, that server failed and is no longer bootable (disk
issues) and the person who originally configured Analysis Services on it so far
has not been able to  remember any details about the setup that has been useful.


texts: Log Reader Agent is not running - The job failed

texts:
I created a transactional replication on PRE-PROD (via TSQL) and everything works as expected. On PROD platform replication is not working. While inspecting Replication monitor, I get this error message for Log Reader Agent:

The job failed.  The Job was invoked by User sa.  The last step to run was step 2 (Run agent.).

Any idea why I get this error?

SQL Agent is running under context of "NT AUTHORITY\NETWORKSERVICE". Configuration and security seems to be identical on PROD and PRE-PROD.

The publication and subscription have been correctly created. Everything is running on a single server, in the context of a user that is sysadmin and db_owner for publication and subscription DB:


texts: how to cache a subset for cascading select queries in mysql

texts:
Here is another database problem I stumbled upon.

I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements to filter the data. Output:

filter 126M
filter 110M
filter 40M
filter 5M
filter 100k

Every single SELECT is highly complex with regex (=no index possible) and multiple comparisons, which is why I want them to query the least amount of rows possible.

There are about 500 unique filters and around 200 constant users. Every filter needs to be run for each user, in total around 100k combinations.

Big question: Is there a way for each subsequent SELECT statement to query only the previous subset?

Example: Filter #5 should only have to query the 5M rows out of query 4 to get those 100k results. At the moment it has to scan through all 200M records.

EDIT
current approach:
cache table

CREATE TABLE IF NOT EXISTS `cache` (
  `filter_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `lookup_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `cache`
 ADD PRIMARY KEY (`filter_id`,`user_id`);

This would contain the relation between individual data-rows from the lookup table and the filters. PLUS I'd be able to use the rrimary index to get all of the lookup_ids from the previous filter.

Query for subsequent filters:

SELECT SUM( column), COUNT(*)
    FROM cache c
    LEFT JOIN lookup_table l ON c.lookup_id= l.id
    WHERE
    c.filter_id = 1
    AND c. user_id= x
    AND l.regex_column = preg_rlike...


texts: Snapshot Isolation vs Read Committed - OLTP and Reporting Databases

texts:
I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database and a separate, more denormalized, reporting database. Assuming we're somewhat disciplined and most of our ad-hoc and reporting type queries actually go to the reporting database, does it sound appropriate that our OLTP database have a default isolation level of Read Committed (we won't need a more stringent isolation level for OLTP) and our reporting database be Snapshot Isolation (probably RCSI)?

My thinking is that if our OLTP database is actually a true OLTP database and not serving double-duty as a reporting DB, we won't need snapshot isolation, and the associated overhead it entails. But snapshot isolation would be desirable on the reporting database so that readers are not blocked by the constant flow of data coming in, and reading the last saved version of a row would be acceptable.


texts: is there a recommended way to update many postgres schemes

texts:
I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions.

the system uses one scheme at a time, as if they were separate databases. So when i install an update i need to update the 15 schemes in the database.

My problem comes when we update the database with changes in functions and triggers, because I need to modify in each function what scheme uses for each scheme; example:

CREATE OR REPLACE FUNCTION cliente11."restablecerComprobante"("numeroComprobante" text)
  RETURNS integer AS
$BODY$
DECLARE idComprobante INTEGER;
DECLARE tipoComprobante TEXT;
DECLARE idCliente INTEGER;
DECLARE fechaComprobante DATE;
DECLARE totalComprobante NUMERIC;
DECLARE estadoComprobante INTEGER;
DECLARE percepcionIIBB NUMERIC;
DECLARE percepcionIIBBxKg NUMERIC;
DECLARE percepcionIVA NUMERIC;
BEGIN

    -- cargo los datos del comprobante
    SELECT id, tipo, cli_id, fecha_comprobante, total_comprobante, percepcion_iibb, percepcion_iibb_kg, percepcion_iva INTO idComprobante, tipoComprobante, idCliente, fechaComprobante, totalComprobante, percepcionIIBB, percepcionIIBBxKg, percepcionIVA FROM cliente11.lista_comprobantes_a_liberar WHERE estado = 0 AND numero_comprobante ILIKE "numeroComprobante";

    -- otras consultas...

    -- genero las comisiones
    SELECT cliente11."generarComisionesVenta"(idComprobante, tipoComprobante) INTO estadoComprobante;

RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Is there a more practical way to update the 15 schemes without having to modify the script to run in each scheme?


texts: Do SSDs reduce the usefulness of Databases

texts:
I only heard about Robert Martin today, and it seems like he's a notable figure in the software world, so I don't mean for my title to appear as if it's a click bait or me putting words in his mouth, but this is simply how I interpreted what I heard from him with my limited experience and understanding.

I was watching a video today (on software architecture), on a talk by Robert C. Martin, and in the latter half of the video, the topic of databases was the main focus.

From my understanding of what he said, it seemed like he was saying that SSDs will reduce the usefulness of databases (considerably).

To explain how I came to this interpretation:

He discussed how with HDDs/spinning disks, retrieving data is slow. However, these days we use SSDs, he noted. He starts off with "RAM is coming" and then continues by mentioning RAM disks, but then says he can't call it RAM disk, so resorts to just saying RAM. So with RAM, we don't need the indexes, because every byte takes the same time to get. (this paragraph is paraphrased by me)

So, him suggesting RAM (as in computer memory) as a replacement for DBs (as that's what I interpreted his statement as) doesn't make sense because that's like saying all the records are in-memory processed in the lifetime of an application (unless you pull from a disk file on demand)

So, I resorted to thinking by RAM, he means SSD. So, in that case, he's saying SSDs reduce the usefulness of databases. He even says "If I was Oracle, I'd be scared. The very foundation of why I exist is evaporating."

From my little understanding of SSDs, unlike HDDs, which are O(n) seek time (I'd think), SSDs are near O(1), or almost random. So, his suggestion was interesting to me, because I've never thought about it like that.
The first time I was introduced to databases a few years ago, when a professor was describing the benefits over regular filesystem,
I concluded the primary role of a database is essentially being a very indexed filesystem (as well as optimizations, caching, concurrent access, etc), thus, if indexes aren't needed in SSD, this kind of does make databases less useful.

Regardless of that though, prefacing that I'm a newb, I find it hard to believe that they become less useful, as everyone still uses DBs as the primary point of their application, instead of pure filesystem, and felt as if he was oversimplifying the role of databases.

Note: I did watch till the end to make sure he didn't say something different.

For reference:
42:22 is when the whole database topic comes up,
43:52 is when he starts off with "Why do we even have databases"

This answer does say SSDs speed DBs up considerably.
This question asks about how optimization is changed.

To TL;DR my question, does the advent of widespread SSD use in the server market (whether it's upcoming or has happened already) reduce the usefulness of databases?

It seemed like what the presenter was trying to convey was that with SSDs, one can store the data on disk, and not have to worry about how slow it would be to retrieve it as with older HDDs, as with SSDs, seek times are near O(1) (I think). So, in the event of that being true, that would hypothetically lose one of the advantages it had: indexing, because the advantage of having indexes for faster seek times is gone.


texts:
I have so tables:

and so data at Language table:

and so data at Text table:

I have to return text for requested language if it exists and text for default language if it does not exist. Is it possible to do that in one query (no while, please)?

Code:

DECLARE @CommentId  bigint = 1
--DECLARE @LanguageCode  nvarchar(2) = 'en' -- "english text" returns
DECLARE @LanguageCode  nvarchar(2) = 'ua'   -- nothing at this moment

SELECT
     t.CommentId
    ,t.TextId
    ,t.[Text]
    ,t.LanguageId
    ,RequestedLanguageId = @LanguageCode
FROM dbo.common_Text t
    INNER JOIN dbo.common_LanguageType l
        ON t.LanguageId = l.LanguageId
WHERE l.Code = @LanguageCode
    AND t.CommentId = @CommentId

Thank you.

ADDED:

If code requests a text in 'ua' (ukrainian) and this is no any text for this language, so it is going to search text for russian. if found - ok, if don't it will look for a text for english. List of languages can vary.


texts: Oracle 19c runInstaller reports PRVF-5311 while checking SSH connectivity

texts:
I am trying to install Oracle 19c RAC on three nodes, running RHEL 8.3. GRID installation was ok. But, Oracle DB SW install using runInstaller(GUI) is stuck with this issue while checking for SSH connectivity between nodes.
Error Reported:
Cause - Either passwordless SSH connectivity is not setup between specified node(s) or they are not reachable. Refer to the logs for more details.  Action - Refer to the logs for more details or contact Oracle Support Services.  More Details
PRVF-5311 : File "/tmp/InstallActions2020-12-24_01-17-15PM/xyzsvr.getFileInfo184599.out" either does not exist or is not accessible on node "xyzsvr".
When I tried runcluvfy, it reports ok.
$ /u01/app/19.0.0/grid/runcluvfy.sh comp admprv -n xyzsvr1,xyzsvr2,xyzsvr3 -o user_equiv -sshonly -verbose
Verifying User Equivalence ...
Node Name                             Status

xyzsvr2                              passed
xyzsvr3                             passed
Verifying Checking user equivalence for user "oracle" on all cluster nodes ...PASSED
From node     To node                   Status

xyzsvr2      xyzsvr1                  SUCCESSFUL
xyzsvr2      xyzsvr3                 SUCCESSFUL
xyzsvr3     xyzsvr1                  SUCCESSFUL
xyzsvr3     xyzsvr2                  SUCCESSFUL
Verifying User Equivalence ...PASSED
Verification of administrative privileges was successful.
CVU operation performed:      administrative privileges
Date:                         Dec 24, 2020 1:44:57 PM
CVU home:                     /u01/app/19.0.0/grid/
User:                         oracle
There is nothing in Oracle support site.


texts: Weird case , how do I delete a cluster under these circumstances

texts:
I would like to know how to delete a cluster with this type of case.

To explain, I created a cluster for two nodes and there was a bug so I had to stop the service on the first node. However after recovering the server, I can no longer see the cluster suddenly like that as if by magic in the failover manager, and when I ping the cluster is always available somewhere.

How can I delete this cluster and redo a new one? Knowing that when I try to create one,  I am told that my second server is already in a cluster while the cluster in question is no longer visible.

Windows Server 2012 R2


texts: Database Design for Employee Information

texts:
I would like to ask if I'm doing this right since it's my first time creating a database model.

Basically what I'm trying do build is an Employee Information model where:

An employee can have multiple addresses but only one can be tagged as the permanent address
An employee can have multiple positions but only one can be tagged as the current position. Same for the employee department

The reason I choose one-to-many relationships on the position and department is to track its histories

An employee can have multiple dependents.

Here's an example of my initial schema

I would appreciate your feedback if I'm doing it right or if you can help me design it in a much better way.


texts: "Limit 1000,25" vs "limit 25 Offset 1000"

texts:
Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem to find what I'm looking for.
Lets say I have 10.000 rows in a table and I want 25 results, from row 1.000. As far as I got so far, I could do both to get the same result:
SELECT id,name,description FROM tablename LIMIT 1000,25
SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000

What I'd like to know is the difference between the two.

Does this actually do the same or is my understanding wrong?
Is one slower/faster in larger tables
Does the result of offset change when I do WHERE column=1 (say column has >100 different values)
Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

I have the feeling offset skips the first X rows found in the database, disregarding sorting and the where.


texts:
I have a bash script that run on a Linux (AWS Centos) server. There's an odd, but annoying warning message that pops up in the stdout whenever the script executes psql: could not find a "psql" to execute.
What is odd is that that the Postgresql command line psql runs just fine! Yet it still generates this error message.
For further context, the bash script is executed from a shell_exec in a php page (fastcgi/nginx). If you run the bash script from the command line, you don't see the warning. But again, even with the warning, psql does actually run properly.
Does anyone know why this warning appears, and how to eliminate it?


texts: MariaDB Uuid as Primary Key Performance Problem / Tweaks

texts:
im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id field to BINARY(16) and created for every row a Uuid v4. Then i've updated the relations with the new uuid (also as BINARY(16)). When i run multiple queries to do a performance benchmark, i have an average performance decrease from 15% on my local docker environment.
The id field is a PRIMARY KEY. The relation fields are marked as index and have Foreign Key constraints.
What can i do to improve the performance?
Most of the tables has round about 200k entries. The whole database has a size of 3.2GB


texts:
I have a user schema like that:
name: String,
nick: String,
email: String,
password: String,

bio: String,
color: String,
followers: [String],
followings: [String],

posts: [{
    author: {
        name: String,
        nick: String
    },
    content: String,
    date: Date,
    simpleDate: String,
    color: String,
    likes: [String]
}]

I want to when user push the like button my backend take the id of post and add user's nick to array that named "likes". So I want to find the object that has post that has this id and add a new string to likes array of it.
User.findOneAndUpdate({'posts._id': req.params.postId},{$push:{posts:{likes:{likes:req.header.authenticatedUser.nick}}}},
    (err,result) => {...});

I tried something like that but it's not working and looks like very wrong.
How can I find and push to nested object like that?
-edit-
When I use the code below
User.findOneAndUpdate({'posts._id': req.params.postId},{"$push":{"posts":{"likes":req.header.authenticatedUser.nick}}},(err,results)=>{
    if(err){
        console.log(err);
    }
});

it adds a likes array to posts. But I want add my usurname to likes array of post that has index 2


texts: Streaming Replication Log is puzzling me

texts:
I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the standby, I see a lot of these:

cp: cannot stat `/mnt/wal_drive/wals/0000000100004BA800000070': No such file or directory
2014-02-28 16:00:37 CET LOG:  record with zero length at 4BA8/70DD79D0
2014-02-28 16:00:37 CET LOG:  started streaming WAL from primary at 4BA8/70000000 on timeline 1
2014-02-28 16:02:59 CET FATAL:  could not receive data from WAL stream: SSL error: sslv3 alert unexpected message

What does each of these mean, and should I worry about any of them?

PS. In the master I see some of these, which I expect to be related to the SSL thing:

2014-02-28 16:10:33 CET LOG:  SSL renegotiation failure
2014-02-28 16:10:33 CET LOG:  SSL error: unexpected record

I'm running postgresql 9.3 on ubuntu 12.04

EDIT

Regarding the WAL logs I have the following setup:

On both my master and my slave I have mounted a network storage in fstab using a cifs command. The directory is chowned to be owned by the postgres user.

On my master I have the following settings in postgresql.conf:

archive_mode = on
archive_command = 'rsync -a %p /mnt/wal_drive/wals/%f </dev/null'

On the slave I have hot_standby = on and the following recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=localhost port=5433 user=replicator password=some_pass'
trigger_file = '/tmp/pgsql.trigger'
restore_command = 'cp -f /mnt/wal_drive/wals/%f %p </dev/null'
archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup /mnt/wal_drive/wals/ %r'

I have an ssh-tunnel forwarding port 5433 of the slave to port 5432 on the master, so connecting on localhost:5433 is actually the master db.


texts: Time sheet Database design with multiple clients

texts:
My Requirement
I need to design a database that handles multiple time sheet formats for different clients. Some of the fields in TimeSheet are the same for some clients and different for others. Also, each client's time sheet may have unique fields. For example:

Client 1 ==> EmpID, Date, Start Time, End Time,Verifier, Supplier Name ...
Client 2 ==> EmpID, Date, No.Of Hours, Project Code, Project Manager ...
Client 3 ==> EmpID, Date, Time In, Time Out, No of Hours Worked, Verifier...
Client 4 ......................................................
Client .. ......................................................

My Query

How should I design the TimeSheet table to accommodate the existing clients or future clients that may get added (not a major concern at this point)

Should all the fields related to all clients be in one table, TimeSheet?
Or should I have a main table TimeSheet with all the common columns, and then additional tables corresponding to each client, to contain the specific fields for that client?
In case I need to have TimeSheet child tables for a future client who would need to have more than one entry per day specific to project tasks how should be incorporated?
Any tips on good practice?


texts: MySQL stress test tool, remote server preferred

texts:
For MySQL stress test there exists programs:
http://dev.mysql.com/doc/mysqltest/2.0/en/test-programs.html

Also mysqlslap exists: http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html

Is it possible to test remote server ?
With proper configuration to connect both servers, is it possible to make stress test from a remote server ?


texts: How do I delete rows from a table on a linked server

texts:
I need to delete rows from a table in several databases on several servers with the same schema. OPENROWSET() does not seem to be the answer.

If I do this:

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

I get the error

Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DELETE FROM  . . .

If I add SELECT 1; to the query so a resultset gets returned like i do below, I get one row one column result (the value of which is BTW not '1'). However, there delete operation does not occur.

SET @sqlStr = '
    SELECT TOP 0 * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
        ''SELECT 1; DELETE FROM [' + @DBName + '].dbo.tblName WHERE foo='bar') AS a';
EXEC (@sqlStr);

What do I need to make the delete happen on the linked server?


texts:
I have to save a lot of documents that I'll show on a web and I have a Mongo database to save them.

I know that Mongo can autogenerate a random ID for each element in a collection and I also know that you can create a hashed index then to increase the speed search.

I'll use the document SHA1 hash (or maybe other) as identifier in the web to each document, and I was wondering if it could be a good idea to use it as custom index on Mongo.

It doesn't matter if someone makes the SHA1 to a random document to search it on the web, I mean, the security it's not a problem because the documents will be public.

I saw that other people have used custom index and they say that it's useful

In my opinion, doing it would  have these advantages:

It's a faster way to find documents, because you can search by the documet hash and not by whole document.
The ID it's already an index so you will save one index.
You may save some storage because you don't need to save the hash separately.
The SHA1 size is smaller than default Mongo id, so you will be able to have more index in memory at same time.

And as disadvantages:

You have to manage the collisions manually. (I may use a "bigger" hash function)
You need to generate the hash by yourself.
it will be harder to short the documents and you may have extra keys.

But there are some disadvantages?


texts: Postgres 10.3 : table grows in size after delete-insert

texts:
I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it.

I have a table (around 3 million rows and 200 columns). In order to populate fresh data this table gets dropped and fully reloaded on a regular basis. I didn't like this approach with full reload so I changed the process a little bit: now the records that require recalculation are deleted and loaded from scratch - other data stays intact. This approach is significantly faster than the full reload, however after deletion and insertion the table bloats horribly (3-5 times depending on the number of records deleted-inserted).

I do understand that the data is not physically removed when DELETE statement is executed. I know that dead tupples remain. My expectation was that if I dropped all the indexes before deleting data, partially deleted data, did VACUUM ANALYZE, reloaded the data and built indexes once again (exactly in this sequence) neither the table itself nor indexes would bloat. But apparently, I was wrong.

Is there anything substantial that I am missing here?

UPDATE: Funny thing is that "SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'table_name' " shows that there are no dead tuples. Now I'm puzzled even more.

UPDATE 2: I figured why the reason behind the bloating. There's another step after the "delta" reload that updates a couple of columns for the whole table (because they can't be loaded in delta mode). This update makes the table grow in size.


texts: Line breaks handled inconsistently by SSMS

texts:
Probably more of a SQL Management Studio question than SQL, per se, but when I run the following simple query:

SELECT results = 'Foo' + CHAR(13) + CHAR(10) + 'Bar'

I appear to get different results depending on whether I choose "Results to Grid" or "Results to Text".

With SSMS set to "Results to Text", I get (as expected):

results
--------
Foo
Bar

(1 row affected)

But with SSMS set to "Results to Grid", then copy and paste the results, I get

Foo  Bar


texts:
Recently I have upgraded my development machine to Windows 10 and SQL Server 2014 SP1 Express (12.0.4213).

Then I noticed that my application sometimes crashes with a timeout exception. It is quite a small .NET application with a 100MB database and few simple queries per second. The application is running at the same machine as the server and connects via shared memory.

I tried to connect to the server with SSMS when yet another timeout occurred, and found that the whole server is unresponsive. It took several attempts just to log in and I was unable to start the activity monitor due to timeouts. Then I tried to restart the server and it took about 1 minute to stop it.

The problem occurs several times per day. Sometimes it continues working normally after a timeout, sometimes I have to restart the server and it restarts quickly, sometimes it hangs for a while during restart.

The server never consumes more than 5% of CPU and 300 MB of memory, even during freezes. Usually there are few hundreds MB of free RAM and few GB of swap space available.

There are no errors or warnings in the event log at the time when the problem occurs.
Over the last week there were few errors: 17300, 17312, 28709. Not sure if they correspond to one of these timeouts. None of these occurred over last 2 days, but the problem continues to arise.

The same application never experienced any problems with SQL Server 2012 SP1 Express on Windows 7.

How this problem can be fixed?

Update

The application database had Auto Close enabled, I disabled it. Also I had to remove some data, so the database is now about 20 MB.

The timeouts on the application now occur less often (about 1 per day). However, the Activity Monitor in SSMS still timeouts almost always. Actions like viewing the database properties also execute very slowly (it takes about 20 seconds).

The sp_readerrorlog command shows nothing except startup info. Example.

I also have monitored the disk activity at the time I got a timeout in the Activity Monitor and the disk load was below 20%.


texts: An advice to design a fact table with a scd as a dimension

texts:
I have a problem of conception and I would like to get your ideas on how I'll be able to do what I am aiming.

My goal is to create a datawarehouse, with three dimensions and one fact table so far.

Dimensions :

Tasks (id, name, status - 0 or 1 -)
Time (as usual)
Organization (Company, dept, office)

I would like to know what would be the best design for my fact table having as main focus my tasks status.

First idea was

task_id
time_id
organization_id
status

With this design, if I do a count of my tasks and if I have two entries one with status set to 0 and another one with status set to 1 for the same task it will return 2 but in fact it is the same tasks.

My conclusion were that Tasks is a SCD, and then I guess I should keep track of the change in the Tasks dimension but how do I design - put my status values into - my fact table then to benefit this.

If I haven't been clear enough please let me know,

UPDATE AFTER COMMENT :

So my measure will be an integer status that is either 0 or 1 that can be updated with the time. At d-day value can be 1 a d - 2 it could have been 0 and I want to store those changes. According to wikipedia then my fact table should have this schema

task_id
time_id_beg
time_id_end
organization_id
status

My metrics or fact that I want to know is for exemple for a d-date how many percentage of the tasks that should have been realized (status = 1) is really realized.

Am I hiting the right way or am I totally lost ?


texts: Indirect constraints (foreign keys amongst three/four tables)

texts:
I have the next layout of tables:

table COMPANY:
- primary key pk_company

table USERS:
- primary key pk_users,
- foreign key (fk_company) references COMPANY (pk_company)

table GROUPS: <br>
- primary key pk_group,
- foreign key (fk_company) references COMPANY (pk_company)

table ROLES:
- primary key pk_role

table RELATIO_UGR:
- foreign key (fk_user) references USERS (pk_user)
- foreign key (fk_group) references GROUPS (pk_group)
- foreign key (fk_role) references ROLES (pk_role)
- Also: UNIQUE KEY [fk_user,fk_group]

This is:

USER ---(n:m)--- GROUP ---(m:1)--- COMPANY
USER ------------(n:1)------------ COMPANY
1 company can have N users (workers)
1 company can have M groups
M groups can have N users (each worker can be in more than one group)
1 user can have only 1 role (read / read-write / admin) in each group.

The thing is that all constraints seem to work in the individual tables, however, the RELATIO_UGR table fails to establish constraints.

Let's suppose we have:

USER1 from GROUP1 and COMPANY1
USER2 from GROUP2 and COMPANY2

The RELATIO_UGR shouldn't allow me to establish (for example) the relationship:

USER2-GROUP1-COMPANY2

Yet, I CAN insert it...

How do I restrict it?


texts:
I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that:

MySQL allows to create a temporary table with the same name as a permanent table. So the temporary table got dropped and not the permanent table. I got really confused with which table I am working.

MySQL version:  5.1.36-enterprise-gpl-pro-log

This is what I'd tested:

mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

 mysql> create temporary table test(id int);
 Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

mysql> drop table test;
 Query OK, 0 rows affected (0.00 sec)

 mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

Is this a bug or is there an alternate way to overcome this?


texts: Create logical replication along with normal replication

texts:
I need to replicate only some of the tables from my Master Postgres.
With Google search, I got to know that it is possible with logical replication from Postgres10.
I already have a read-only replica and now I want to add another replica but with only selected Tables in that new replica.
   A--->B(normal replica with all data from A)
(Master)
   |
   |--->C(logical replica with only selected tables from A)

How do I configure this?
My master is set to wal_level = 'hot_standby'. B is my standby replica.
Now I want to add a new replica C which should contain replicas of only selected tables.
Now, for C if I set wal_level=logical this will affect the A-B. The addition of C should not affect the current A-B setup.


texts: Why low cpu usage on sql server developer edition 2014

texts:
The CPU usage is low, around 15 %. CPU is 4770K. The SQL server data file is split in 8 parts and are stored on EVO 970 SSD, along with tempdb (also split in 8 parts).
The temdlog and database log are on another SSD (read/write potential around 500MB/s).
I don't see any high disk usage in task manager below (10 MB/s), and RAM usage is not high either (have 32 GB).
Max parallelization is set to 8.

It seems SQL server is mostly using 1 core, but I can't figure out why.
Does not seem to be bound by disk nor ram, so how can I get it to work a bit harder (use more CPU)?

Update

Tested Dan Guzman's query and confirmed the cpu is more utilised although it is hard to have time to see in task manager (query time 2 seconds).
Good to know the developer version isn't locked to one core.

Tested the queries from Dan Brown's link. The parallelized version got my cpu usage over 95 % so it seems case is closed; the low cpu usage is because of the query.

I've a lot of correlated subqueries (select in select), which does not look good for performance. For maintenance it's been easiest to calculate several statistics in one query, but it seems only one core is used in that way.
I tested splitting them so that I only calculate one metric per query, and that way sql server starts using more cores.

In the future I think I will test the performance of Spark for the same queries and see how it compares to sql server. Perhaps also by running the queries against sql server through spark on several workers (in that case I need to split the work by partitioning according to e.g. modulo of pk).

For the interested:
what I need to calculate are various metrics for races, so that for each race and each competitor I fetch it's top record e.g. during last 3 months, last 6 months etc. or average time last 6 months.
When the query time is around 10 hours it starts affecting the analytics work I want to do (one iteration to change or correct something takes too long).


texts:
I have the following table:

+--+---------+-------+--------------+--------------+
|ID|HEADER_ID|VERSION|FROM_DATE_TIME|  TO_DATE_TIME|
+--+---------+-------+--------------+--------------+
| 1|      100|      1|03-06-14 12:56|17-12-14 09:08|
| 2|      100|      2|17-04-14 09:09|14-04-15 14:55|
| 3|      100|      3|14-04-15 14:56|          NULL|
| 4|      101|      1|02-09-13 11:33|02-09-14 11:32|
| 5|      101|      2|02-09-14 11:33|30-09-14 17:08|
| 6|      101|      2|15-12-14 07:51|15-01-14 07:51|
| 7|      102|      1|17-03-11 09:44|17-03-12 09:43|
| 8|      102|      2|17-03-12 09:44|17-03-13 09:43|
+--+---------+-------+--------------+--------------+

This table contains the versions in time for a certain header.

The id column which is the primary key.
The header_id column, which is a foreign key to a header table.
The version column, which defines the version (this does not have to be unique).
The from_date_time column, which defines the start date and time that this version was active.
The to_date_time column, which defines the end date and time that this version was active.

What I would like to achieve is the following:

The combination header_id and the period between the from_date_time and the to_date_time should be unique. So periods should not overlap.

Is it possible? Is it possible with MySQL? How to implement in MySQL? How to implement in other DBMS if MySQL is not possible.


texts: Increase query performance where large number of values appear in primary key

texts:
I have a query where there are numerous values passed in

This is a heavily simplified version of the query

SELECT Id, Name, <other cols..>
FROM Table
<various joins etc.>
WHERE Id in (1,2,3,4,5,6,7....1000,1001,1002 etc.)

I have noticed that when passing in fewer values the query is quick and uses the required index to return the data, however after a certain amount of values the query uses a constant scan and a hash table to join to the main result set which is killing the performance.

Is there a better way of returning this data?


texts: Implementing json_object_agg() in Postgres 9.3

texts:
I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table click_activity of data that looks like

user | offer | clicks
-----|-------|--------
fred |coupons| 3
fred |cars   | 1
john |coupons| 2

But I want to turn it into this: (aggregate the activity per user)

user | activity
-----|----------
fred | {"coupons": 3, "cars": 1}
john | {"coupons": 2}

I think that the json_object_agg() function of Postgres 9.4 would do this perfectly, all I would have to call is

select user, json_object_agg(offer, clicks) from click_activity group by 1

Is there a way to do this in 9.3? Thank you!


texts: Hardware Cluster to VMware, is vMotion an alternative for clustering?

texts:
We are migrating DB servers from "real" clusters to VMware. The question is with VMware how do you get similar functionality in VMware? Things like failover etc?

Is vMotion the answer? I wonder what other people do, what is considered best practice?


texts: Error while running sp_blitz in 2008 SQL Server and lower versions

texts:
I have created an SSIS package to run sp_blitz in my production servers. The stored procedure runs fine for servers having a version higher than 2012 but fails for 2008 and 2005.

Please find the subsequent error message that I received while running the package.

  [SP Call [50]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB
  error has occurred. Error code: 0x80040E14. An OLE DB record is
  available.  Source: "Microsoft SQL Server Native Client 11.0"
  Hresult: 0x80040E14  Description: "Statement(s) could not be
  prepared.". An OLE DB record is available.  Source: "Microsoft SQL
  Server Native Client 11.0"  Hresult: 0x80040E14  Description:
  "Incorrect syntax near 'RESULT'.".

I have googled about the error and found that EXECUTE WITH RESULT SETS feature was introduced in 2012. Thus running the stored procedure with RESULT SETS fails in lower versions. I am using the below query to execute the stored procedure.

EXEC sp_Blitz WITH RESULT SETS (
(
Priority TINYINT ,
FindingsGroup VARCHAR(50),
Finding VARCHAR(200),
DatabaseName NVARCHAR(128),
URL VARCHAR(200),
Details NVARCHAR(4000),
QueryPlan XML  NULL,
QueryPlanFiltered [NVARCHAR](MAX) NULL,
CheckID INT,
Server_Name sql_variant,
[Server_Version] varchar(128),
[Server_Edition] sql_variant,
[Server_Product_Level] sql_variant,
[Server_Product_Version] sql_variant
));

Half of my production servers are 2008 and 2005. Is there any alternative command or method that can be used so that this can run in lower versions as well.


texts: What is the best way to import this non-standard text file into SQL Server?

texts:
I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest name in the data set + 1 space. This makes each day's files have different column start and end points.

Does anyone have a clever way to handle importing this file on a daily basis? Changing the source program is not an option.

I am willing to modify the file before import.

I am willing to use almost any tool to include, but not limited to, SSIS, C#, PowerShell.

I am looking for the least "messy" solution with high-reliability.

Examples

A/R                                      Patient                                  PICASI, LINDA  00691685             90409843             Inpatient

Revenue                                  Patient                                  SIMPSON, RHASIRA B 00000000             00000000             Emergency


texts: DB2 developers can't create table in own schema but SQL and Oracle devs can. Justified?

texts:
As a developer new to DB2, I was surprised when I discovered that I could not create my own tables in the development database environment. So, I posed a question to the db2 DBA. I don’t want to be too much of a nuisance by questioning policy and was hoping to get a better understanding of whether our policy of preventing developers from creating tables is justified for DB2.

Question:
I understand that you must follow policy, but do you know why our company might create a DB2-only policy to prevent DB2 developers from creating tables in their own schema while simultaneously allowing SQL Server and Oracle developers to do so?

Answer:
The mainframe is amore controlled environment than Oracle and SQL.   All applications within our company share the DB2 subsystems.   In Oracle or SQL, each application has its own instance and their testing would not impact another instance.
-- DB2 mainframe does not create multiple tables in the same file like Oracle.  So, on the mainframe if you were able to create tables in test, you could potentially use up all the dasd if you created a huge table and if you were to test against that huge table, it could blow out the subsystem sort space, edm pool,etc. that is sized for a test environment.
--DB2 Mainframe applications share subsystems and all system resources.  Each test db2 subsystem is sized according to the function of that DB2 subsystem…  DB2T – testing, DB2F – System testing, DB2B – Production Support.  They all have different sizing allowance.

Question to SO:
Re: “you could potentially use up all the dasd if you created a huge table”
Doesn’t DB2 have the ability to limit space usage by schema to prevent a developer from using up all of the DASD (which I presume is Disk Access Storage device/aka disk space)?
Looking up the acronym EDM and finding “Environmental Descriptor Manager” I presume that this is what contains all of the table definitions, like a Master database. Is this a valid concern that is specific to DB2?

From the DBA’s answer, I generally understand that our architecture is such that each environment, eg, TEST, UAT, etc, does not run in a separate instance and one instance can affect the other, but it sound like if this is the case, then we already have this issue given that developers have the ability to insert millions of rows into tables that were created for them. Isn’t the real threat the size of the tables that I create and rather than the ability to create a table? I would think that a DB2 DBA should be able to limit developers to a safe level.


texts: LEAST with 'if' condition brings timestamp instead of date


texts: create a backup table with million records

texts:
I want to move old records from TABLEA to TABLEA_AUDIT. TABLEA have around  1.5 million records and it have two nested tables.

No of Records : 1557951
Size :  1024 MB

I tried it by,

1.using Create table as select * from

CREATE TABLE
TABLEA_AUDIT
COLUMN TABLEA_STAGE NOT SUBSTITUTABLE AT ALL LEVELS
NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES,
NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS
AS
SELECT *
FROM
TABLEA
WHERE COULMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';

Result : 2hours of waiting and didnt get any result

2.Tried CREATE TABLE AS with no logging by follwoing below blog.
http://www.dba-oracle.com/t_fast_copy_data_oracle_table.htm

Result : 2hours of waiting and didnt get any result

3.Developed a new procedure to copy the records and created a new DBMS_JOB

EXECUTE IMMEDIATE 'CREATE TABLE TABLEA_AUDIT AS SELECT * FROM TABLEA';

Result : Job is running for more than 2hours and no result.

4.Created the table and developed a procedure to insert bulk records

set serveroutput on size unlimited
set timing on
declare
type audit_type is table of TABLEA%rowtype;
v_type audit_type;
CURSOR temp_cur is
select  *
FROM TABLEA a
WHERE COLUMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';
BEGIN

    OPEN  temp_cur;
    / collect data in the collection /
    FETCH temp_cur BULK COLLECT INTO v_type;
    / close the pointer /
    CLOSE temp_cur;

    FORALL i in v_type.first .. v_type.last
    INSERT INTO TABLEA_AUDIT VALUES v_type(i);

    FORALL i in v_type.first .. v_type.last
    DELETE  FROM TABLEA WHERE PRIMARY_KEY_COL = v_type(i).PRIMARY_KEY_COL;

    COMMIT;
END;
/

Ps: We have tried the same with out any indexes in new AUDIT (TABLEA) table.
Result :  Block running for more than a hour.

  Without Nested table I can copy 7,716,204 records in less than a
  minute.  Size : 960.28125 MB

Thanks.


texts: Time dimension or timestamp in fact table?

texts:
Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both?

I am building a data warehouse, and need to represent the time of day that events occur at, down to the one second granularity. I want to roll data up; plotting a graph of number of events per hour of the day, for example.

Kimball's "The Data Warehouse Toolkit", has a design for a time of day dimension. A more recent blog post recommends not doing that, and using a timestamp in the fact table instead:

http://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/

If I go with timestamps in the fact table, will it still be easy/fast to roll up by the hour?

Any other trade-offs to consider in making this choice?


texts: Does the Query cost in Sql Server account for buffering?

texts: If I am comparing 2 queries by checking their relative "Query cost" as a high level indicator of performance, does the buffer status have any effect on the values shown in the execution plan?


texts:
I recently applied the latest SQL Server CUs (Cumulative updates) for their current SPs (Service Packs) on our SQL Servers.  I'm getting the sense that I actually really need to update all of them to the latest SP + CU.  For example, we have a 2014 SP2 server, which now has the latest CU for SP2 (SP2 CU18).  Should I update it to the latest SP + CU (SP3 CU4)?
Thanks,
-Peter


texts:
My app is making the following psql query, and it is running extremely slow:
SELECT COUNT(*)
FROM (
  SELECT 1 AS one
  FROM "large_table"
  WHERE "large_table"."user_id" = 123
  ORDER BY "large_table"."id" desc
  LIMIT 1 OFFSET 30
) subquery_for_count;

When I change the ORDER BY to ASC, it runs like 100x quicker. I have the default primary key index on id, and I've experimented with adding an additional index for the id in desc order, however it didn't seem to make a difference.
When I run Explain Analyze, I see that it is using an index scan backwards on the slow query (desc). I tried manually disabling index scans for my session, and found that the query ran in 40seconds instead of 2 minutes, which is a noticeable improvement.
Any idea on what I can do to try and improve the speed of this query when sorting by DESC? I've read that for b-tree indices, it should generally give you the same performance irregardless of sort order, but that does not seem to be the case.


texts: What's the risk of keeping a table with row size > 8126 in MySQL 5.7?

texts:
I just upgraded from MySQL 5.6 to 5.7, and had the bad surprise to discover that a WordPress plugin table contains a ridiculous 438 fields, and exceeds the row size limit of 8126 bytes.

Here is the warning I got when running mysql_upgrade:

  Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

This was just a warning though, and the table is still there. I can't create a new table with the same structure, but interestingly enough, I can keep the old table and use it (at least, SELECT from it).

I've read RolandoMySQLDBA's and Bill Karwin's answers on similar topics, and no solution can help me:

changing to ROW_FORMAT=DYNAMIC is not enough, as the table is full of inline UTF-8 VARCHAR fields that alone exceed the max size
modifying the structure of the table is out of the question, as it's part of a third-party software, and that would be like opening a can of worms

I've reported the error to the plugin developers and hope they'll fix the table soon, but in the meantime, I'm stuck with this outlaw table.

So the questions are:

How can MySQL work with a table that does not respect the InnoDB storage rules, when it does not allow to create such a table?
Is there any risk to keep the table in its current state?
Are there limitations on the type of queries I can run on this table?
If I replicate this server to another MySQL 5.7 server, will the replication fail because of this table?

I upgraded from MySQL 5.6. I did try DYNAMIC and COMPRESSED formats, but no luck. I calculated the size of the integer + varchar fields, they're totaling 17,059 bytes, so I have no chance to succeed this way.

The plugin is called Photo Gallery and the faulty table is wp_bwg_theme.


texts: Retrieving only the last row of a table, in the absence of a primary key

texts:
I have a table that has 3 columns:

Column 1: contains XML data (that contains a timestamp value)
Column 2: contains the file name(with the path to it) corresponding to  XML data record (as to which file that XML data is stored in)
Column 3: contains the offset of the XML data within that file

For instance, different file names can be

C:\Extended Events\ReadWrite_0_130863592475800000
C:\Extended Events\ReadWrite_0_130863685030030000
C:\Extended Events\ReadWrite_0_130864300903760000

That is, the names assigned to the files are in incremental order.

Further each file name has different offset values in it, corresponding to the XML data value, returned above.

For instance,

C:\Extended Events\ReadWrite_0_130863592475800000 can have offset values 24576, 34816, 62976 (in an increasing order too)

Then, C:\Extended Events\ReadWrite_0_130864300903760000 can have offset values for itself independent of the above offset values.

I want to retrieve the file name and offset value corresponding to the last record returned i.e. if a total of 9 rows have been returned, I need the file name and offset value corresponding only to the 9th row.
I also want it to be optimal.

Using the MAX function isn't helping my case. I tried max(file_name) and it simply opened the Documents folder.


texts: No CREATE_TIMEDATE column, can I still export records inserted within last 24 hours?

texts:
A database was designed almost 10 years ago, now I may need to export records inserted from the last 24 hours. Back then, the table design did not include CREATE_TIMEDATE col for these tables.

Is there any luck that database servers (in this case SQL Server 2005), have a system hidden TIMEDATE attribute for all table records so I can export all records within 24 hours?

Or is my only solution is to save last primary key id at midnight and then 23.99 hours later, export from last primary key id to current primary key id?


texts: Select random database records between subset of records having the same count(*) in a query

texts:
I have this simple query:

  SELECT `value`, count(*) as `noHits`
    FROM `topic`
    WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."')
    GROUP BY `value`
    ORDER BY count(*) DESC LIMIT 150

It generates a list of records such as:

keyword1, 100
keyword2, 90
keyword3, 40
keyword4, 40
keyword5, 40
keyword6, 40
...
keyword1500, 40

My challenge is to select a list of top 150 records ordered by count(*) DESC however when there are more than 1 value having the same count(*) as in the list above keyword3 to keyword1500 select them randomly from the database.

It seems as if the selection is not random from the tests I made.

The result is used to generate a wordcloud and I do not want the same keywords to always be selected (out of thousands) when they have the same count(*)


texts:
I have created some non-clustered index on my tables in SQL Server 2014.

Is there any way to Rebuild (and Reorganize) the indexes automatically? I mean is it possible to schedule them to be built daily, weekly, ... ?


texts: Two different unique constraints for 1 table?

texts:
I must enforce uniqueness within my table, But I cannot seem to find anywhere online(mysql manual,forums,stack overflow) talk about two unique constraints within one table.

Why you might ask I need two?

example

+----+-----+-----+--------+----------+
| PK | pid | car | serial | created  |
+----+-----+-----+--------+----------+
| 1  |  0  |  2  | 88888  | 02/04/12 | -> good data
| 2  |  0  |  2  | 22222  | 02/02/12 | -> bad data
| 3  |  0  |  5  | 88888  | 02/08/12 | -> bad data
+----+-----+-----+--------+----------+

pid and car must be unique and appear only once.
pid and serial must be unique and appear only once.

At first I thought to add a constraint UNIQUE (pid,car,color), but with this constraint we could still enter false data as in the example,

row[2](pid => 0, serial => 22222)
or
row[3](pid => 0, car => 5)

In other words, car or serial can only be inserted once for any one pid.

Thanks

PS, Sorry if my explanation isnt so clear, I will have to re illustrate, possibly by finding a better example too.


texts: novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK

texts:
I've been wondering if following approach is bad or really very bad:).

Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN KEY) [we know we can, because we do this, but is it acceptable:)]?

Question#2: if we decide to use indicator_id (which in indicator_details table is not PK) would it have an important impact on performance?

Understanding SQL approach correctly, the best option would be to use iid (iid as FOREIGN KEY) in indicator_values table instead of indicator_id, but as always project has some limitation (like the way how users insert data - more below), thus we trying to find a compromise solution.

I aware of that this could cause data integrity issues, but solution without FK would be much user-friendly (since users don't have to worry about inserting into indicator_values table correct rows - rows with correct iid), especially that in our case data integrity is not crucial. Moreover if the only way (in our case) of inserting data by users is that is
preceded by removing all rows from table, approach with FKs would cause deleting all rows (caused by ON DELETE ...) from indicator_values during indicator_details table update (again - update means: remove all rows then insert new data), so this would be time consuming.

DB:

indicator_values table

+----------+--------------+------+-------+
| vid (PK) | indicator_id | year | value |
+----------+--------------+------+-------+
|     1    | AACA         | 2001 |    10 |
|     2    | bbb          | 2001 |   100 |
|     3    | ccc          | 2001 |    10 |
|     4    | AACA         | 2002 |    20 |
|     5    | bbb          | 2002 |   200 |
|     6    | ccc          | 2002 |   300 |

indicator_details table

+----------+--------------+------------------+-----------------------------+
| iid (PK) | indicator_id |     desc         |          full_text          |
+----------+--------------+------------------+-----------------------------+
|  1       | AACA         | AACA is super    | Lorem ipsum dolor sit amet  |
|  2       | bbb          | bbb is extra     | Maecenas sagittis ultricies |
|  3       | ccc          | ccc is superb    | Praesent tincidunt nec orci |


texts:
I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.

In server name field I specify serverIP\DBNODE2 and SQL Server credentials.

I'm 100% sure that the credentials are correct, but I'm not sure about the server name part.

When I try to connect I get the following error:

Any ideas what am I doing wrong?

EDIT: I've checked:

Started SQL Server Browser service
SQL Server service for MSSQLSERVER is running
Firewall is off
Remote connections are enabled in Configuration Manager
TCP ports are enabled

Server name is DBNODE2.
SQL Server instance name is MSSQLSERVER.

The only thing, that I don't understand why it's asking server name? This server is in remote geographical location, so I don't think, that it could find the right server without IP address.
I've tried bunch of different combinations and none of them worked:

IP\DBNODE2
IP\MSSQLSERVER
DBNODE2\MSSQLSERVER
IP
DBNODE2
Also tried double backslashes
Etc.

EDIT2:
SQL Server service is running under 1433 port.
I've used Port Query tool, which I've found here.

    =============================================
    Starting portqry.exe -n {IP} -e 1433 -p TCP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    TCP port 1433 (ms-sql-s service): LISTENING
    =============================================
    Starting portqry.exe -n {IP} -e 1434 -p UDP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
    Sending SQL Server query to UDP port 1434...
    Server's response:
    ServerName DBNODE2
    InstanceName MSSQLSERVER
    IsClustered No
    Version 11.0.3000.0
    tcp 1433
    ==== End of SQL Server query response ====

ANSWER: Probably server IP address is not allowed in server name field. The workaround is to modify hosts file like @MarkIannucci decribed below.


texts:
Nightly we dump our Production DB and load it into our Staging and Dev environments.

A bash script is taking care of that and the steps to achieve this are the following:

download the dump from the cloud
drop the active connections to the DB
revoke any connections to the DB
drop the DB
reload (recreate) the DB

This is the query I use for dropping the active conns:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$DB' AND pid <> pg_backend_pid();

And the one for revoking incoming conns:

REVOKE CONNECT ON DATABASE $DB FROM PUBLIC;

The problem is that almost every night, the script fails with the following: ERROR:  database "my_db" is being accessed by other users

And I'm not sure why is this happening since I reject the existing and then revoke any further connections.

Is it possible that the execution from dropping the connections to dropping the DB is too fast, which doesn't give enough time for some connections to finish getting dropped? I am adding a 2 sec delay after the REVOKE to test this out tonight, but I was wondering if there is a less hackish, and more sophisticated way of solving this.


texts:
I have recently taken over managing a MySQL database running on Windows Server 2008 R2. It is running 5.5.37 Community Edition. When it is started, it includes the following lines:

Thread, Type, Details
Column count of mysql.events_waits_current is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history_long is wrong. Expected 16, found 19. The table is probably corrupted
Native table 'performance_schema'.'threads' has the wrong structure
Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type int(11), found type bigint(20) unsigned.
Column count of mysql.file_summary_by_event_name is wrong. Expected 5, found 23. The table is probably corrupted
Column count of mysql.file_summary_by_instance is wrong. Expected 6, found 25. The table is probably corrupted
Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.
Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.

I tried running mysql_upgrade, but get the following result:

Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqlcheck.exe
The system cannot find the file specified.
FATAL ERROR: Upgrade failed

The paths point to the correct locations. I can run mysql.exe and mysqlcheck.exe from those locations. I am running from an Administrator-level command prompt.

I have tried running mysqlcheck.exe with the parameter --all-databases, and everything checks out as OK. But those errors still remain when I restart MySQL.

Any ideas how to get rid of these errors?


texts:
I have Oracle RAC on the very powerful servers and it can be said that these servers have extra memory,cpu and storage :) ...so my servers are much more powerful than it is needed.

My manager and other administrators(who do not have enough resources) need my RAC to be moved to Virtual Machine,to utilize RAC servers for their needs.

I need your advise... What to do? Do I have to move my RAC on virtual machines?  if not what arguments should I say, to prove that this idea is stupid?

Thank you very much, waiting your suggestions and clever arguments...


texts:
I have a really old piece of industrial equipment that connects from windows professional to a sql server 2008r2 on win 2008R2, not completely patched.
We cannot replace the windows 2000, but would like to connect to a sql server 2016.
This is the connection string in the udl file
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=blabla;Persist Security Info=True;User ID=LVD;Initial Catalog=LVDLASER;Data Source=someserver
It works if I point to the old server.
If I change the servername, test it on a win 10 machine, it works.
On the win2000 I get this:

Test connection failed because of an error in initializing provider.
[DBNETLIB]ConnectionOpen (SECDoClientHandshake()).SSL Security error.
Any suggestion is very much appreciated.


texts: How to replicate MySQL table in different servers?

texts:
I am searching for some solution for the following problem:

I have two MySQL Database servers in different locations.
I need copy a specific table from Server A into the same table of the Server B (duplicate table).
This is a live table and it receives 3-6 insert/updates operations per minute (on average)
I need that the information in both tables is synchronized in real time, each change in the table of Server A should be made in the table of Server B.
The table of Server B will be used as only read table for a dashboard tool, so the goal is that dashboard tools does not query the original table and original server for security reasons.

Added requirement:

As described above, the goal is to have a replica of the table of Server A into Server B, so to publish it and this could be read for a dashboard tool.
This table has confidential information so some columns of the table must not be replicated, for instance, columns for client names, national ids, phones,...

My first idea is do this through triggers but these do not work with different servers.

Replica master-slave could mark the way, but I don't know if it's possible to alter schemas in the destiny table.

Can this be done with native tools? If there is some commercial solution, buying it is not a problem.


texts:
I have a virtualised SQL Server 2005 (SP3) that reported the following message:

Database_Name, Database_Id, File_Id, Event_Type, Event_Type_Desc, Error_Count, Last_Update_Date
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:033
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:043
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:050
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:057
...

It is the second time in the past month that I've seen this error on this particular server - and in both cases it was in tempdb, with Event_type = 3 (i.e. Torn Page) only. It has never been Event_type = 1, which corresponds to an 823 (disk error) or 824 error.

Running the command:

DBCC CHECKDB WITH ALL_ERRORMSGS, PHYSICAL_ONLY

returns:

DBCC results for 'tempdb'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'tempdb'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In other words, everything seems fine, but why then is suspect pages getting recorded?

Other actions taken:

HDDScan / chkdsk run to make sure its not a drive error (there is also nothing relevant recorded in the windows event logs around the time of the error).
PAGE_VERIFY is currently set to NONE (default?) - investigated the possibility of setting it to CHECKSUM, but that's only possible for tempdb in SQL Server 2008 onwards.
Stopped SQL server, deleted existing tempdb files, restarted it (based upon a comment in
the question -> How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no error
Read the similar questions (to the right) but none of them are relevant.

Is there anything else I should be checking / doing, or should I simply disregard this because its TempDB (i.e. transient data)?

Thanks for any assistance/input.


texts:
I'm going to receive a large group of CSV files daily. Additionally I have 1 billion records of data (from those CSV files) but it's not a fixed number (1b records daily). They're going to be growing and I need to store them in a DB. Also there are some extra points:

There's no update
There's no join and relationship
Select a bunch of rows and group by
Write intensive is 9 times more than read (we don't need to read data it must be a storage of data)
I don't need normalisation

I had a bench between MySQL InnoDB and MyISAM. MyISAM was more better than InnoDB (because I have no normalisation) but MySQL is not a good approach because I have no relation.

Also I've checked MongoDB finally it took 50GB of data and used 150Gb of storage disk!

So I think I need a NoSQL DB which can do distributed write and support above extra points. What do you prefer?

I know I can use CSV files but I need a db approach not a file approach. I have a lot of tools and advantages like version update, bug fixes, security stuff, read and write performance, replica, etc in a database. I cannot have something like group by (or where, ...) in CSV files and I have no time to implement them with CSV files.


texts:
I am using 11.2 version of oracle.
I want to de-duplicate from LISTAGG.

below is existing data in oracle db:

Speaker | Qualification | Product
P           A              P1
P           B              P2
P           C              P3
P           D              P1
P           E              P2
Q           A              P1
Q           B              P2
Q           C              P1

Want below data:

Spkeaker | Product
P           ;P1;P2;P3;
Q           ;P1;P2;

Can any one help please.


texts: Data Extract and submit across the network

texts:
I've been given a somewhat vague requirement regarding a data extract process which I need some help with.  I'm fairly new to database warehousing etc. but I will try to explain everything relevant.

We are beginning a project to set up a shared services domain between our site (A) and another site (B).  Currently we both have independent and unrelated systems to store the same thing.  Site A has the best system and so the plan is to move our database (SQL Server) to the shared services domain and then both sites will use the single database.  We will both have high-speed dedicated network bandwidth to the shared services domain.

The infrastructure is being taken care of by network guys but I am going to have to implement the business reporting side of things.  The requirements around this are:

Database X has data 'belonging' to both site A and site B (but referring to the same nature of information)
Site B's data needs to be extracted and manipulated into some kind of report format (Site B's data can be logically separated from Site A's data based on some values)
The data needs to be sent to Site B over the network
The process needs to be quick

I have some basic knowledge of SSIS, which just because of what I've read on MSDN and forums seems like the best way to go, but actually I'm not aware of any alternatives to SSIS.

What are the options for this requirement?

Thanks


texts: Queries are faster in SSMS 2008R2 as compared to SSMS 2016

texts:
I am connecting to a database server(MSSQL 2008R2) using SSMS 2016 and for simple queries even like "USE DBNAME", it's taking 4-5 secs. However time stat shows "CPU time = 0 ms,  elapsed time = 0 ms", not sure why?

On the other hand, If I connect the same server using SSMS 2008R2, it gets executed instantly.

I compared client statistics for both SSMS2008R2 AND 2016 but both shows almost same stats.

Why my SSMS2016 is behaving like this?


texts:
I keep hearing sp_who2 is deprecated soon, unsupported. We are on SQL Server 2016 Enterprise.

What is a good official Microsoft replacement item or DMV which team should utilize instead of sp_who2?

I heard one is sys.dm_exec_sessions?


texts: Prevent reserved words being used for Column Names in Oracle 12c via a trigger

texts: Is there a way to prevent Oracle from permitting reserved words in column names via a trigger? This solution (after I fix the Declare typo) has compilation errors and doesn't work. I want to ensure reserved words are never used, even if quoted. System-level way to enforce that reserved words may not be used for Oracle column names?


texts: Trying to repair query store, but sp_query_store_consistency_check does not exist

texts:
The query store on our SQL Server 2016 Standard (SP2 CU6) database has gone into ERROR state and we are now trying to get it back online. All of the articles I've seen regarding troubleshooting QS (e.g. this one, and even Microsoft's own docs) suggest to do the following:

ALTER DATABASE [QDS] SET QUERY_STORE = OFF
exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [QDS] SET QUERY_STORE = ON
ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

But the procedure sp_query_store_consistency_check doesn't seem to exist in any form on our instance. What are we missing?


texts:
I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics.

 Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64)
 Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition
 (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

MAX Memory set at 215GB from 244GB x1e.2xlarge AWS EC2
There are around 260 databases.
Locked Pages in Memory is on.
Page File size is 8GB.

Memory optimized tables that uses less than 10GB of memory.

  Failed to reserve pages: FAIL_PAGE_RESERVATION

  Error: 8645, Severity: 17, State: 1. A timeout occurred while waiting
  for memory resources to execute the query in resource pool 'internal'
  (1). Rerun the query.

  Error: 701, Severity: 17, State: 135. There is insufficient system
  memory in resource pool 'internal' to run this query.

After multiple errors like these, the server shutdown itself.

Page life expectancy is 1297050, no memory grants pending, doesn't appear to have any memory pressure.

Any ideas on this issue?


texts: MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

texts:
I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct errors in the original data entry, and view the overall composite dataset (changes overlaid on original data), without making any modifications to the table of original_data.

My fields are a mix of int, varchar, and datetime; nothing bizarre.

The system currently accomplishes this with the following:

  A table original_data (which is never modified). This is indexed
  on a datetime field.
  A table modifications, which includes all fields that
  original_data has. Each row in modifications references the primary
  key of the row in original_data that is modified.
  A view mods_overlay that joins original_data to modifications on
  a key, displaying modified data (where it exists) in place of original
  data. I'm using CASE WHEN statements to return fields from
  modifications where they exists for a particular row, else return the
  field from original_data.

So far, so good! The problem is, I have millions of rows, and one of the fields I need to modify is the datetime field on which original_data is indexed. As soon as my mods_overlay view overlays this datetime field, I can no longer efficiently select my data on a specific period of time; runtime for select statements increases from a few seconds to 30+ minutes. MySQL does not support indexing for views, nor does it support materialized views, either of which could help here.

One workaround: I could implement mods_overlay as a table that is pre-computed overnight, but then new modifications cannot be seen until the next day, and I'm not sure if this will be acceptable.

Are there other ways to solve this problem without making significant tooling changes?

Stated differently: is there a best practice in MySQL for providing a way to overlay a set of modifications on a set of original data (in a table that is never modified), while indexing on a field that can itself have modifications?


texts: I have a OS user 'test' who doesn't belong to dba group or oinstall group. I need to access the database using sqlplus but I'm unable to access the sqlplus even after setting oracle home ans path and tnsnames. Any solution?


texts: Add user to sql server with the required permission

texts: In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users have full permission while some other 5 users with limited access to some database, and other users only read permission. In future i may add other users also. So i was thinking of doing it in simple way than giving permission for every user as it requires giving permission to each database every time a new user is created. So can i create a set of rules or group and then add a new user to that group or rules? Do i have any kind of facility in sql server which will make this happen?


texts:
I have a stored procedure that is using the SUBSTRING function in the WHERE clause.

SELECT DISTINCT
   ColumnBB,
   ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;

How can I prevent an index scan for this query and make it execute quickly?