texts: What would be most elegant way to replicate data contents periodically into a MySQL table?

texts:
I need to parse contents from an API route and insert into a MySQL database. The aim of parsing contents is to replicate the data from the API to my database table.

I need to check API response every day (at a specified time) using scheduled cron jobs and should fetch and insert all available records into the database.There could be changes in the response every time and it should be reflected as provided in the API.

There isn't any fields which could be considered for making a unique ID as well.

I can't search and sort as its not an updating task alone, hence rather than 'updating' of records, we should doing 'replication' of data from the API. We need to consider,

deletion of removed records from the API response,
updating modified record fields and
Also the insertion of new records.

Consider an example:

On day 1,

Let the records available with the API be A1, A2, A3, A4

There could be duplicate entries among this records .

We will be parsing the API response and insert ever record available as it is. The  script will fetch records (A1, A2, A3, A4) and insert it to our table.

Our table will hence contain records :( A1, A2, A3, A4)

Consider on the next day,

Let the records available with API be A1,A2,A3,A5,A6  -

Possible cases:

There could updated field values for each records A1, A2, and A3 than
day 1
A4 is removed from the API response now
New records A5,A6 are added now.

In this case our aim is to update the table such that, it should only have records A1, A2, A3, A5, A6 with their updated values.

The data is expected to be structured.There are specific fields from API,which are to be parsed and fetched.We can't estimate how often the changes would be made,and its asked to make update every day.

The response field values might change but not the structure.The fields for which the values should be fetched will remain same,only the change would affect the field values.

Currently there are about 2000 records ,which could increase up to 5000 soon.

There should not be any down time as the service from this table is used in real time by another application.I am parsing and inserting API response into a table which acts as a master table for another application.That application needs to work without any downtime,i.e; There should be least possible downtime even while we are doing some operations(recreating tables) over the table.

Sample API Response:

[
    {
        "company": "XYZ",
        "company_id": 123,
        "owner": null,
        "owner_id": null
    },
    {
        "company": null,
        "company_id": null,
        "owner": "ABC",
        "owner_id": 321
    },
    {
        "company": "XYZ",
        "company_id": 123,
        "owner": null,
        "owner_id": null
    },
    {
        "company": null,
        "company_id": null,
        "owner": "PQR",
        "owner_id": 100
    }
]

Database table should replicate the API response as given - irrespective of any errors/duplicates available in the API results.The API responses cannot be predicted before hand,also there isn't any direct fields which could be treated as a UNIQUE id.

I am using PHP-MySQL.API response is in JSON format. I had seen a similar question in SO,but it doesn't caters my requirement well and it also doesn't have an accepted answer.

https://stackoverflow.com/questions/1972944/what-is-the-best-way-to-periodically-load-data-into-table

From the above question it seems using some kind of temporary tables would be needed I my case as well.

What would be the most elegant way to approach this problem considering safety,performance and no downtime.What would be best MySQL storage engine to be used in this scenario-(InnoDB/MYISAM)?
Please advice.


texts: Find the greatest difference between each unique record with different timestamps


texts: How to get one row out of many rows with same column id

texts:
I have a use case in which I have three tables.

Courses Table

Course_ Id   Name
 1   MathMatics
 2   Physics

Teacher Table

 Teacher_ID  Teacher_Name
    1    AAA
    2    BBB

TeacherVsCourses Table

 id  Course_id  Teacher_id
 1   1   1
 2   2   1
 3   1   2

My application is programmed using the MVC design pattern of ASP.NET. I am saving individual records into the database table as shown below:

What I want in my view is instead of two rows for Teacher-id=1, I need 1 row with comma separated courses.

How can I achieve this?


texts:
I want to add innodb_file_per_table statement to my.cnf file.

Where is my.cnf?

Could I change this file when I have an online site?


texts: SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

texts:
I have a very simple query:

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

against this table:

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

I have a simple index on the UserID column (no cols included) and everything works just fine and super-fast.

But once every 5-8 days I see timeouts in that part of the application. So I go to investigate in the Query Store and I see that the server stops using my index and reverts to a stupid "clustered scan". Removing the temp table doesn't help.

In order to resolve this - I reset the plan cache for this particular query (just for the record here's how I do this)

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

And then starts working normally again.

Execution plans: slow fast

I've been scratching my head for days now... Any ideas?


texts: Alternatives for SSMS solutions & projects?

texts:
According to the official SQL Server Documentation, the use of "Solutions, Projects, and Items" is deprecated.  A conspicuous (though somewhat cryptic) warning banner at the top of the MSDN page states:

  "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

Unfortunately, no suggestions for alternative storage/container strategies are offered. So what is the recommended way for storing and packaging the various scripts, queries, and files that make up your database application? I would also be very interested to know if any of you are acually using the Solutions or Projects framework in your current work, and what you use them for.

[Note: I realize that I could use VS2010 for this functionality, but I am interested in SSMS-based approaches (for the reasons expressed in the answer to this question).]


texts: Query to delete all rows which are not referenced in any other tables

texts:
Let's say i have a table called images. Images table is referenced in many other tables (movies, books, tv_series) through a foreign key.

My question is if it is possible to write a query which will delete all orphaned rows without specifying exact tables (eg JOIN movies ON ...). This is important because if new table which references images will be introduced it will make the query outdated. If the query will be executed without changes it could delete images which are used in new table.


texts: Deadlock occur in a delete insert scenario

texts:
<deadlock-list>
  <deadlock victim="process14012e08">
    <process-list>
      <process id="process14012e08" taskpriority="0" logused="148"
               waitresource="PAGE: 28:1:762341" waittime="6583"
               ownerId="54542271281" transactionname="DELETE"
               lasttranstarted="2013-06-22T01:04:20.450" XDES="0x64de49950"
               lockMode="S" schedulerid="26" kpid="13336" status="suspended" spid="1239"
               sbid="0" ecid="0" priority="0" trancount="2"
               lastbatchstarted="2013-06-22T01:00:19.120"
               lastbatchcompleted="2013-06-22T01:00:19.117" hostpid="15675"
               isolationlevel="read committed (2)" xactid="54542271281" currentdb="28"
               lockTimeout="4294967295" clientoption1="671350816" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="3" stmtstart="134" stmtend="332"
                 sqlhandle="0x02000000e5eb1a38144fa5c164b3c876cc2c8da059bf3bb1">
            delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          </frame>
        </executionStack>
        <inputbuf>
          set rowcount 10000
          while 1 = 1
          begin delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          if @@rowcount = 0
          break
          end
          set rowcount 0
        </inputbuf>
      </process>
      <process id="process10031288" taskpriority="0" logused="912"
               waitresource="OBJECT: 28:731149650:16 " waittime="40270"
               ownerId="54542276360" transactionname="insertRows"
               lasttranstarted="2013-06-22T01:04:23.753" XDES="0x1641ef9400"
               lockMode="IS" schedulerid="17" kpid="29104" status="suspended" spid="351"
               sbid="0" ecid="0" priority="0" trancount="2"
               lastbatchstarted="2013-06-22T01:04:23.753"
               lastbatchcompleted="2013-06-22T01:04:23.660"
               isolationlevel="read committed (2)" xactid="54542276360"
               lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="check_grid_col" line="1" stmtstart="226" stmtend="376"
                 sqlhandle="0x03001c00fe2706376157a70026a000000000000000000000">
            SELECT @retval = COUNT(*) from grid_col where grid_col_id = @grid_col
          </frame>
          <frame procname="adhoc" line="1" stmtstart="34376" stmtend="35270"
                 sqlhandle="0x02000000434dff26c45f91c6fdb640bbbf3a4a276f6ac605">
            INSERT INTO grid (grid_id,grid_col_id,value) VALUES ( @P2 , @grid_col_id,@P3)
          </frame>
        </executionStack>
        <inputbuf>
          (@P0 bigint,@P1 int,@P2 char(300))
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <pagelock fileid="1" pageid="762341" dbid="28" objectname="grid"
                id="lock1cb9187500" mode="IX" associatedObjectId="72057594070695936">
        <owner-list>
          <owner id="process10031288" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process14012e08" mode="S" requestType="wait" />
        </waiter-list>
      </pagelock>
      <objectlock lockPartition="16" objid="731149650" subresource="FULL"
                  dbid="28" objectname="grid_col" id="lock12e6c78f00" mode="X"
                  associatedObjectId="731149650">
        <owner-list>
          <owner id="process14012e08" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process10031288" mode="IS" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>


texts:
I'm trying to debug remotely pl/sql. But I can't  - database returns me an error. What should I do to fix this and start debugging ?

    ORA-30683: failure establishing connection to debugger
    ORA-12541: TNS:no listener
    ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    ORA-06512: at line 1

UPD
I'm trying to debug procedure on remote server. On local computer I have only sql developer installed.


texts: Designing a database structure for companies and stock owners

texts:
I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the information for every owner in "Sears INC". That is the idea. But I am having a really hard time trying to structure this database. I only have limited experience and knowledge about this subject so any guidance would be greatly appreciated.

Now I was first thinking about making a table called companies, and give them all unique ID's and make a table for every company. Then inside those tables would be all the owners with their unique ID. That would link to their information. I have tried to visualize this here:

Now I know that creating a table for every company would be tedious but I cant think of any other way of doing this. As you all know each company might have the same owner and therefore it would make sense of using Owner_ID to identify each owner.

I have all the other data in a CVS file that I could easily import in PostgreSQL after I have structured the database.

Any help would be greatly appreciated.

TL:DR; Creating a database of stock owners that will be searchable by company name, and need help to structure the database for maximum efficacy.

A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.


texts: How to add `--sslAllowInvalidCertificates` on mongodb uri?

texts: I have a mongodb server requires x.509 certificate. So I have to use --sslPEMKeyFile and --sslCAFile on mongo shell command to connect to that server. It works fine. In my dev environment, I am using --sslAllowInvalidCertificates parameter. It works fine. But I wonder how I can use --sslAllowInvalidCertificates through nodejs driver. I have checked this link http://mongodb.github.io/node-mongodb-native/2.2/reference/connecting/connection-settings/ but it doesn't have the option for invalid certificates.


texts: How to clean DBMIRROR_EVENTS_QUEUE process on broken mirror?

texts: We setup mirror (on SQL Server 2008 R2), but it fails during the setup. After that the mirror was moved on another server. My problem is that it left some process, because on that server I see active DBMIRROR_EVENTS_QUEUE wait. How to find what causes that wait and clean it?


texts: How should I build a DB to hold data about another database

texts:
I know that this is a strange question. I need ideas and/or advice, not necessarily "how-to"s.

I work with data from a 3rd party tool/database. I do not have permissions to modify this production database. Any changes or additional information that is needed that cannot go into this production database has to be housed somewhere else.

I have tables whose data has some sort of relationship to each other. For example: Let's say I have a "Caller" table that contains the caller's name and the company that they work for, and I have a "Company" table. Normally, I would use the PK on the Company table in the Caller table as an FK. In this case, that is not possible. On top of that, the Company Names may not be EXACTLY the same. For example: Kim Smith might have "ABC Distributing" as her company in the Caller table, while the Company table has the name as "ABC Dist".

I need to create a database to "support" this other database. In addition to the problem listed above, sometimes companies change names. The powers-that-be want the historical records attached to the company's name at that period in time. So if "ABC Dist" changed their name to "ABC Distribution and Manufacturing" in January 2016, the powers-that-be want the records for that company before Jan 1, 2016 to have the "ABC Dist" name, while records AFTER Jan 1, 2016 to have the new name. We now have TWO records for the same company, with two PKs. Again, this is not something that I can change. If I could rewrite this 3rd party tool, I would.

So, I need a way to keep callers associated with the correct company in the company table and a way to associate multiple lines in the same company table together (company name changes).

I know that I'll have to write an ETL to keep these two databases synchronized. What I need is ideas on how to design the support DB. Do I copy company names over or just use the PK for everything?

I hope that I've made the issues clear and understandable. I'm really frustrated with this mess, and need a way to tame it a bit.

OH! Also, I need suggestions on how to keep track of when changes are made to the production database. No one bothers to tell me changes have occurred until something downstream breaks. (Using MS SQL Server 2008 R2)

Should I have submitted this under the developers' StackOverflow?


texts: SQL Mirroring Message "The mirroring connection to "address" has timed out for database "database"

texts:
Executing under SQL Server 2016 SP2 (SP2-CU2-GDR) (KB4458621) enterprise edition. Receiving error message

  "The mirroring connection to "address" has timed out for database".

Do not have a witness server.  We manually fail over if required.  I have followed the advice I have found online to change the Partner Timeout value. Still getting the message every 21 minutes no matter what value I have in partner timeout. For example, my current Partner Timeout value is 3000. If I am understanding correctly that is 50 minutes.  Network team says there are no problems with the network.  I am open to any suggestions.


texts:
I am building a web application for project management which will be hosted on my servers. My application design is like this

  1 - user register

  2 - domain name will be created i.e abc.domain-name.com

I am planning to have one different  database for each user with their username as prefix of the tables .
i.e

  DB name :  abc_db\

      Tables   - abc_projects
               -  abc_users
               - etc....

So is this a scalable and reliable solution for high  traffic site..?


texts: Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10

texts:
The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error

  psql: FATAL:  database locale is incompatible with operating system
  DETAIL:  The database was initialized with LC_COLLATE "en_US.UTF-8",
  which is not recognized by setlocale(). HINT:  Recreate the database
  with another locale or install the missing locale.

The locale available in Win10 are

                          List of databases
   Name    | Owner      | Encoding | Collate | Ctype |
-----------+------------+----------+---------+-------+-
 postgres  | postgres   | UTF8     | en-US   | en-US |

                                           List of databases
   Name    | Owner    | Encoding |          Collate           |           Ctype            |
-----------+----------+----------+----------------------------+----------------------------+
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

But the above locale are not taking when doing the Basebackup. Any fix for this issue.


texts: How to restore SQL Server database to a particular time?

texts:
Is it possible for me to restore a SQL Server 2019 database up to a particular datetime? The database backup is full backup.
The database is full recovery mode, and only one transaction log backup after the full backup.
Thanks.


texts: Can I change which index a primary key uses?

texts:
I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change the table to use the auto-increment id column for the clustered index.

I can't explicitly drop and recreate the index on the id, as it is used to enforce the PK.
I can't drop the PK, because it is referenced by foreign keys.
I would like to avoid dropping the foreign keys, because re-adding them will result in a constraint check on the existing rows, and there are a lot of rows.

I thought the following steps might work:

Drop the existing clustered index
Create a clustered index on the id
Somehow tell the table to use that index for enforcing the PK constraint
Remove the old non-clustered index
Rename the clustered index to match the convention

Is this possible? And if so, is it a good idea?


texts: Order to disable constraint in a SQL Server Database

texts:
I have a DB that every day delete all the data and load new one at the night. To do this an SP disable all constraint with sp_msforeachtable execution. I created some new objects (Schemas, SP, Tables, etc.) and after this, starts to crash sending this message "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "[Constraint_Name]". The conflict occurred in database "[DatabaseExample]", table "[Schema].[Table]", column '[Column_X]'"

I decided to disable it manually, but i don't know which is the right order to disabled without using hard coding.

First, do i need to disable Unique Keys or Foreign Keys?, if a FK referenced to another table i should start disabling the reference table?


texts: What are the differences between Mirroring and Failover Clustering and when is each one recommended over the other?

texts:
What are the main differences between Mirroring and Failover Clustering?

What kind of problem each one solves and in what kind of scenarios is each one recommended over the other?


texts:
How do you estimate the total time reindexing an entire table will take in PostgreSQL?

I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 indexes.

I'm seeing a problem where, after a night of processes loading data into the table, queries to the table are terribly slow for about 3-6 hours after the loading ends. However, these queries miraculously become fast again after 6 hours post-load.

Is this temporary slowness caused by Postgres updating indexes in the background? How do I speed up this processing? I can't find any parameters for controlling this.

I was thinking about running a REINDEX on the table immediately after loading ends, but I'm not sure how long this will take, and I'm worried it might take longer than the original load and will only worsen performance.

My table stores dozens of descriptors about a customer, and the indexes (many of them partial indexes) are for speeding up very routine queries run by users.

The database is nearly a terabyte, so I rarely load the entire thing to a dev environment. Instead, I have a script that copies a few thousand records.


texts: Spool in Shell script not working for me

texts:
#!/bin/sh

echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null
SET SERVEROUTPUT ON
SET FEED OFF
spool ech.txt create
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
spool off
EOF

if [ $? -ne 0 ]
then
   echo " ech.sh sql   Error"
   exit 1
else
   echo " ech.sh  Successful"
fi

I am getting an SQL error message. Any ideas?


texts: How to cache query for re-use in following UNION of queries?

texts:
I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB).

This is the query I want to save:

SELECT `event`.id, event.name
FROM attendant
INNER JOIN event ON attendant.fk_event = event.id
WHERE attendant.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM organizer
INNER JOIN event ON organizer.fk_event = event.id
WHERE organizer.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM invitation
INNER JOIN event ON invitation.fk_event = event.id
WHERE invitation.fk_user_to = 1

And then perform multiple queries like

SELECT id FROM savedquery WHERE .....

UNION

SELECT id FROM savedquery WHERE .....;

I've found TEMPORARY TABLEs but I don't think that I could use it since I need to perform this query for multiple different users at the time they run the request for events.

VIEW is also named, so I suppose there is the same problem.

What's the best solution to do this please?


texts: In PostgreSQL, how to deal with error messages that show almost no context?

texts:
Today I realized a number of points where PostgreSQL (v10.3) is rather lackluster and sparse in its error messages.

The first point is illustrated by this code:

drop schema if exists X cascade;
create schema X;

create domain X.an_illegal_regex as text check ( value ~ '(' );

create table X.table_with_illegal_constraint (
  a text,
  constraint "column a must have a bogus value" check ( a::X.an_illegal_regex = a ) );

select * from X.table_with_illegal_constraint;

insert into X.table_with_illegal_constraint values
  ( 'xxx' ),
  -- ( 'xxx' ),
  ( 'foo' ),
  ( 'xyx' );

This code will throw with

psql:db/experiments/pg-error-fail-illegal-regex.sql:17: ERROR:
invalid regular expression: parentheses () not balanced

There are several problems with this error message:

FAILURE: the error is really in line 5 where a syntactically invalid
RegEx is created; the fact that it is a RegEx and not a general
string is obvious from the semantics of the ~ (tilde) operator at
that point in time.

FAILURE: the offending RegEx is not referred to and not quoted in the error
message. As such, it could be anywhere in my many, many kLOCs big
DB definition. I cannot even search the RegEx with a RegEx because all
I know is some parenthesis is missing, somewhere: RegExes cannot match
parentheses, and PG RegExes do not have a unique syntactic marker to them.

FAILURE: before the insert statement, everything runs dandy. We could have
built an entire data warehouse application on top of a table definition
that can never be syntactically processed but which will only fail when
someone accidentally tries to insert a line.

FAILURE: I can select from a table with a syntactically invalid definition.

The second point is related:

drop schema if exists X cascade;
create schema X;

create domain X.a_legal_regex as text check ( value ~ '^x' );

create table X.table_with_constraints (
  a text,
  constraint "column a must start with x" check ( a::X.a_legal_regex = a ),
  constraint "field b must have 3 characters" check ( character_length( a ) = 3 ) );

insert into X.table_with_constraints values
  ( 'xxx' ),
  ( 'foo' ),        /* A: violates first constraint */
  -- ( 'xxxx' ),       /* B: violates second constraint */
  ( 'xyx' );

With only line B active, this gives:

psql:db/experiments/pg-error-fail-no-constraint-name.sql:16:
ERROR:  new row for relation "table_with_constraints" violates
check constraint "field b must have 3 characters"
DETAIL:  Failing row contains (xxxx).

SUCCESS: we get the name of the relation and the name of the violated
rule.

SUCCESS: the offending piece of data is quoted.

FAILURE: we don't get the full name of the relation, which is
"X"."table_with_constraints". Neither do we get the name of the
column that received the offending value.

Lastly, with only line A (not line B) active:

psql:db/experiments/pg-error-fail-no-constraint-name.sql:16:
ERROR:  value for domain x.a_legal_regex violates check constraint "a_legal_regex_check"

FAILURE: no reference to the affected table, column is made.

FAILURE: no reference to the offending piece of data is made.

FAILURE: no reference to the offended constraint is made ("column a must start with x").

What are the best practices or workarounds for the above shortcomings? I've been trying for several hours to figure out what causes an error message a la value for domain xxx violates check constraint "xxx_check" by rewriting table definitions,
inserting data row by row and so on, to no avail. What I need is a full chain of the objects (column -> table -> constraint -> domain -> check) that are involved in the error.


texts:
I have 3 tables

about 250,000 records.
table1 is MyISAM, add fullindex with title and content, add index with pid.
table2 and table3 are InnoDB, add index with pid.

Only query table3, just cost 0.04 seconds.

select * from table3
WHERE MATCH (title,content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY pid

but query like this, cost 16.87 seconds.

SELECT *
FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
LEFT JOIN table3 ON table1.pid = table3.pid
WHERE MATCH (table3.title, table3.content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY table3.pid

I make an EXPLAIN plan for the 2nd query, return:

id select_type  table     type  possible_keys    key    key_len    ref               rows      Extra
1  SIMPLE       table1    ALL    pid             NULL   NULL       NULL              201497    Using temporary; Using filesort
1  SIMPLE       table2    ref    pid             pid    32         mydb.table1.pid   1
1  SIMPLE       table3    ref    pid             pid    32         mydb.table2.pid   222309    Using where

Why is the 2nd query slow?
How can I optimize it?

Thanks.


texts:
Currently I am working on fixing an issue with the Java-DB2 connection leak. For which I require the CLI Trace logs. In my db2cli.ini I have added the below parameters, but still I am not getting the the trace logs.

    [COMMON]
    JDBCTrace=1
    JDBCTraceFlush=1
    JDBCTracePathName=C:\tracejdbc

Earlier we were using DB2 9.7 in which I was able to get the logs for the above changes. And now we are using DB2 10.5.3. I am trying it myself. Meanwhile it would be helpful on getting an Expert advice.


texts:
We've got a stored procedure that's running in a SQL Server agent job on multiple servers.  It has the little block of code to log debugging information.  It's the same code on both machines, but on one machine the message is coming out garbled and the other clean.  I don't see why it's behaving differently on the different servers.

DECLARE @NumToProcess VARCHAR(5)
SELECT @NumToProcess = COUNT(*) FROM @ProcessingIDs
IF(@NumToProcess = 0)
BEGIN
    RAISERROR('Nothing to process', 10, 1) WITH NOWAIT
    RETURN
END
ELSE
BEGIN
    DECLARE @Message VARCHAR(100)
    SET @Message = 'Processing ' + @NumToProcess + ' events'
    RAISERROR(@Message, 10, 1) WITH NOWAIT
END

On one server I get the expected result in the Agent job history when there are records to process (e.g "Processing 105 events  [SQLSTATE 01000] (Message 50000)")

But on the other server I see slight variations on this

...vents [SQLSTATE 01000] (Message 50000)
...ts [SQLSTATE 01000] (Message 50000)
...ents [SQLSTATE 01000] (Message 50000)

Not only is the "Processing " replaced with ..., parts of the word after the number gets consumed too.

The code looks dirt simple. Can someone explain why the one server could be producing this result?


texts: Can I create a computed column that requires input to select?

texts:
I have a scenario in a very old and very large application, where I have a table representing a type of resource:

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

This table is selected from in hundreds of different places, including stored procedures and dynamic SQL in the application code.

A team recently updated this resource's name to be localized, and their approach is pretty straight forward. There is a new table containing the localized names, and a 'default' language ID on the resource table, for when the name isn't localized for the requested language:

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

Most procs have an @user_language_id parameter, so the logic for choosing the name to return is simple: take resource_local.name matching language_id = @user_language_id if it exists, otherwise resource_local.name matching language_id = resource.default_language_id if it exists, otherwise take resource.name.

Unfortunately, this turns the logic to select the correct name into something like this:

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

All of the hundreds of places that try to select resource.name are having to be updated with this logic, which has turned this project into a massive effort across the entire organization, as each team needs to update their SQL to use this logic. This also causes maintainability issues, as any new developers dealing with this table need to know that they can't just use the name column.

It's too late now, but I'm curious: is there any better way to approach this, so that selecting the name column from resource will just 'do the right thing' based on the @user_language_id variable (if it exists)?


texts: In PostgreSQL, what is the behavior of the bit(n) -> "char" cast?

texts:
Using PostgreSQL if I cast

65::bit(8)::"char"

I get 0, if I however cast

65::bit(8)::int::"char"

I get A as I would expect.

SELECT
  v,
  v::bit(8)::"char" AS noint,
  v::bit(8)::int::"char" AS "viaInt"
FROM (VALUES (65)) AS t(v);
 v  | noint | viaInt
----+-------+--------
 65 | 0     | A
(1 row)


texts: Create Linked Server to AlwaysOn Availability Group Listener

texts:
I have an alwayson availability group setup using SQL 2014 and Windows Server 2012 R2. There are 3 nodes in the group. The SQL Service is running as a Managed Service account on each node (1 MSA for each server).

I am trying to make a linked server on each of the nodes to point to the current active node. So lets say the three servers are called Server1, Server2 and Server3, and the AlwaysOn availability group is called SQLAAG_PROD. I want to create the linked server to SQLAAG_PROD.

I am able to create a linked server to each individual node (e.g. Server1 with a link to server2 and Server3) and it works fine using the security option Be made using the login's current security context.

However when I try to create a linked server to the group listener I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

If I change the security mode to use the SA password for the current active server then everything works, but for obvious security reasons that's not an acceptable solution.

I've read a lot about needing to setup SPN's and allow accounts for delegation. I have turned on Trust this computer for delegation to any service (Kerberos only) on the SQLAAG_PROD object in Active Directory but still have the same problem.

Has anyone seen this before, and/or is there a guide on how to set this up?

===UPDATE===

Not sure if it helps, but when I run

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

when connected directly to the node I get KERBEROS, however if I connect to SQLAAG_PROD and run the same query then I get NTLM


texts:
I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of data in the parent table from before I created partitions.

The parent and children exist in different schemas:

parent: public.history_uint
children: partitions.history_uint_p2015_mm_dd

Here is how I created the partitions.

How to TRUNCATE the parent but not the children?


texts: Is there a way to see if a SQL 2012 Replication publication is marked for initilazation and a way to unmark it?

texts:
We have a SQL (2102) Replication Database with a Realtime DB being the Publisher and a Historical Db as the Subscriber. All of the Tables/Articles have the setting of Do Not Delete. Thereby the Realtime DB is maintained with a max data of say 2 weeks but the historical DB will just keep growing. We need to add a couple of tables/articles and have seen the mini-snapshot discussed and it does work fine. However I have come to a worrisome condition. If ever a snapshot is initialized, all of the history will be lost! Bad design I suppose. But is there a way to check to see if a publication is marked for initialization and a way to clear it?

Thanks!


texts: What are the best field data types for a table of musical keys

texts:
I'm fairly new to relational databases. I understand how fields may have int or text data structures, but when it goes beyond that, I feel a bit out of my element.

I have a table called: keys

It is supposed to hold information on which key a classical music composition was written in. An example entry could be: E minor.

What I did is create two columns for the key table: name which is the name of the key, taking on values ranging from A-G, as well as tonality which tells us whether the composition was major or minor.

It currently has this form:

Question: What is the best way to construct my keys table (in terms of efficiency/scalability) and what data structures should the fields have?

I think it would make sense to have the tonality field be a binary. But does binary in this case need to be 1's and 0's? Or is there an efficient way to include the whole string minor or major? I'm not sure if I need to create another table to relate it, or if it can be efficiently done with 1. Also is text an appropriate field type for key.name? It can only take on 7 values: A, B, C, D, E, F, G. (My data do not include sharps or flats).


texts: Simplest way to log in as Oracle user different from schema user and still see all tables,... of schema?

texts:
What is the simplest way to log in as user X and see all tables,... of schema Y as if they belonged to X?

With an Oracle 11g database, I would like to log into the database using user X but all the data (tables, views, procedures, ...) would be stored in Schema Y.

All the code that accesses the tables is guaranteed to use unqualified names (TBL_NAME, not Y.TBL_NAME)

Searching for "schema alias" points to CREATE SYNONYM, but that would have to be done for each DML object, right? And since I only have unqualified calls, it seems a login trigger with ALTER SESSION SET CURRENT_SCHEMA = ...; should do the trick?

Any other options?


texts: Oracle 11g stuck at installation in oracle linux no error msg only showing | and stuck

texts: oracle not install on centos7 while installation it is stuck at 84% no error msg showing


texts:
The following VB.Net code runs fine on SQL Server but gives an error on Access:

sql = "SELECT sr.itemcode, sr.itemname, SUM(CASE WHEN sr.transdate='4/14/2016' THEN sr.temunits] END) AS [Units Sold], " & _
      "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' GROUP BY sr.itemcode, sr.itemname"

The error is:

IErrorInfo.GetDescription Failed with E_FAIL(0x80004005), -System.Data


texts:
Following a video tutorial on oracle wallets: https://youtu.be/LLZzUNb9ALU
The db server is run from oracle's official docker container.
The steps I have done are as follows:

On server shell, ran sql plus as sysdba:

$ sqlplus / as sysdba

Created a user:

SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> CREATE USER fred identified by flintstone;
SQL> GRANT CONNECT, RESOURCE, DBA to fred;

Tested logon:

$ sqlplus fred/flintstone

SQL> show user;
User is "fred"

Created a wallet at default location: $ORACLE_BASE/admin/$ORACLE_SID/wallet

$ orapki wallet create -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -auto_login -pwd MyWalletPass

Created a wallet profile for user fred:

$ mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -createCredential $ORACLE_SID fred flintstone

Tried to login sqlplus:

$ sqlplus /@$ORACLE_SID

This fails. Got the error ORA-01017: invalid username/password; logon denied
Not able to understand whats wrong. The final goal is to use this wallet for a nodejs application with external auth (node-oracledb). But I think if I can fix step 6 I should be able to use from nodejs.


texts:
I'm wondering if I'm doing this right. I have the following tables

Users
Paths
Blocks

  A Path consists of blocks, A User has one Path, A Block can only belong to one path (so no many-to-many between blocks and paths is needed

This is my blocks table

+----+---------+-------------+
| id | path_id | description |
+----+---------+-------------+
|  1 |       1 | block 1     |
|  1 |       2 | block 2     |
+----+---------+-------------+

I have the following scheme for the user_paths

+---------+---------+
| path_id | user_id |
+---------+---------+
|       1 |       1 |
|       1 |       2 |
+---------+---------+

And this is my user_blocks table

+----------+---------+-----------+
| block_id | user_id | completed |
+----------+---------+-----------+
|        1 |       1 | true      |
|        2 |       1 | true      |
+----------+---------+-----------+

This is my 'simplified' scheme in picture

Now I'm wondering if this is the right way to go about this? Because

Blocks belong to Paths, but still I'm joining the blocks with the Users table straight.

I feel like this isn't right?

But otherwise it isn't possible to check wich user has completed which block, right?

Any idea's or tips?
Thank you!


texts: "Thread was being aborted" error when inserting 25k rows

texts:
I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a stored procedure. There's an object containing data for each row in the table.

Here's the stored procedure:

ALTER PROCEDURE [dbo].[BankImport]
    -- Add the parameters for the stored procedure here
    @bankName nvarchar(50) = null,
    @shortBankName nvarchar(50) = null,
    @branchName nvarchar(50) = null,
    @sortCode int,
    @addresseeName nchar(60) = null,
    @postalName nchar(60) = null,
    @addressLine1 nchar(100) = null,
    @addressLine2 nchar(80) = null,
    @cityOrTown nchar(50) = null,
    @areaOrCounty nchar(60) = null,
    @postCode nchar(12) = null,
    @fasterPayments nchar(10) = null,
    @directDebits nchar(10) = null,
    @chaps nchar(10) = null,
    @chequeCreditClearing nchar(10) = null

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM [dbo].[banks] WHERE sortCode = @sortCode)

        UPDATE [dbo].[banks]
        SET @bankName = bankName,
            @shortBankName = shortBankName,
            @branchName = branchName,
            @sortCode = sortCode,
            @addresseeName = addresseeName,
            @postalName = postalName,
            @addressLine1 = addressLine1,
            @addressLine2 = addressLine2,
            @cityOrTown = cityOrTown,
            @areaOrCounty = areaOrCounty,
            @postCode = postCode,
            @fasterPayments = fasterPayments,
            @directDebits = directDebits,
            @chaps = chaps,
            @chequeCreditClearing = chequeCreditClearing
        WHERE sortCode = @sortCode

    ELSE

        INSERT INTO [dbo].[banks] VALUES (
            @bankName,
            @shortBankName,
            @branchName,
            @sortCode,
            @addresseeName,
            @postalName,
            @addressLine1,
            @addressLine2,
            @cityOrTown,
            @areaOrCounty,
            @postCode,
            @fasterPayments,
            @directDebits,
            @chaps,
            @chequeCreditClearing
        )

END

It works okay on smaller datasets, but the full-size contains each branch for each bank in the UK. It manages to get through to about 15k rows before giving this error:

Thread was being aborted.

Is this likely to be more of a time-out issue, or inefficiency, or is there something else that I'm missing?


texts: Accessing file share from ETL package in SQL Always-On configuration

texts:
How can I access the file share from ETL?

Here are my environment details:

I have 2 nodes in a windows cluster. node 01 and node 02
SQL Server 2014 is installed on both the nodes.
Both are Windows Server 2012 R2 machines
I have file share created in Azure portal and mapped as G: in both the nodes (01 and 02)

My ETL package is using C# code. It wants to read and write files on one shared folder for processing. I want it to use shared drive G.

Problem Statement: One of the nodes can be down at any time, so I cannot hard code as below:

//node01/G

Can I use file share feature (role) in a failover cluster? if yes, where will that new drive be stored?

I saw the post Accessing an Azure Storage Account File Share from an ASP.NET Application on Stack Overflow, but that requires code changes in all the ETL packages. Is that is the only way?


texts:
Oracle 2 node RAC 11G R2

I have a query (this is a simplified version, but still exhibits the same issue as the larger query) that looks like this:

SELECT
   t1.trip_id,
   t1.origin,
   t2.sighting_date
FROM trips t1
JOIN trip_details t2 on t1.trip_id = t2.trip_id
WHERE
--don't the get the information if the trip *only* belongs to customerid 99
99 <> any(select customerid from customer_trip ct where ct.trip_id = t1.trip_id);

trip has trip level information - trip_id, origin, destination, etc
trip_details has sightings for the trips - trip_id, sighting_date, sighting_location, etc
customer_trip has which trips belong to which customers - some trips may be shared between customers and some may not,  so the basic structure is just customer_id, trip_id

The problem is that it keeps joining trip and trip_details first, and then limiting by the 99<> any.. statement.  The 99 <> any.. statement greatly reduces the rows from trip and would reduce the rows looked up in trip_details (which is very, very large - 100x trip).  So, ideally, I'd like it join trips and the customertrip subquery first, and then do trip_details.

But I can't figure out if there's a way to specify a join order such that it does it in that order.  The LEADING hint doesn't seem to have a way to reference the subquery.

Stats are all up to date.


texts:
I have a stored procedure that is being called very frequently.
For some database servers it is as often as 20 times per second.
Unfortunately because different clients call this procedure, I can't (without significant rearchitecture of the application) chunk the queries into table updates.

Recently the database performance has fell through the floor, a very simple update takes 30-90 seconds instead of milliseconds.

By far the (looking at resource waits on the servers), Locks contribute the vast vast majority of the waits (its 150x the time of the second contendor).

The stored procedure does some validation then either inserts or updates an existing record (for now all of them are updates).  If the validation fails it RAISERROR and the errors is passed all the way back to the client (the main obstacle to why it can't be chunked easily).

The stored procedure is

UPDATE dbo.TblHeartbeat
SET
    LastCheckedIn = GETUTCDATE(),
    NumberOfCheckIns = NumberOfCheckIns + 1,
    LastCheckInVersion = @Version,
    IPAddress = @IPAddress
WHERE
    MachineID = @MachineKey

I suspect that changing this to a WITH(ROWLOCK) will resolve the issue.
There doesn't seem to be a lot of science around locks though.
Opinions vary from, SQL knows what it's doing don't force it to do something, to it will ignore hints and you should actually remove the page lock from the table to force it to use row locks.

I am looking for,

What is the correct approach here.
What science can I place behind this change
How do I calculate / appreciate the impact of this change.
Will SQL ignore hints?


texts: What database to choose for 1TB of time series?

texts:
I'm saving about 3GB per day of financial orderbook data to a database (currently Mysql). In the coming weeks, I'm going to save trade and quote (bids/asks) data too.

Data is structured, but I'm doing many searches on it, for example get all orderbooks by stock symbol (which is very slow somehow). Or get all bids/asks by symbol. Currently, a query might take 5 seconds or so (select X where ticker like Y), and the size is only 20GB. In the future, at 1TB, I'm a bit concerned regarding speed per query.

What are some recommendations for cost efficient but also performant solutions for this task? I'm currently running a Mysql database on Digitalocean. Appreciate any help, also any tips regarding what to look into (indexing, sql vs nosql, performance tips, which specific databases would be better suited).


texts: Getting total number of payments and percentage

texts:
I have a payments table with the following fields:

id (int)
status (successful, failed, refunded)
amount (int)
pdate (Y-m-d format)

I need to write a query to get, for each day how many payments were successful, how many failed and how many were refunded, with the percentage for that day. My current query is this:

select pdate, status, count(*) as total
from payments
group by pdate, status

The query successfully returns how many payments were processed on each day and the status:

2019-04-01,  successful,  10
2019-04-01,  failed,       5
2019-04-01,  refunded,     5
2019-04-02,  successful,  12
2019-04-02,  failed,       5
2019-04-02,  refunded,     3

But I don't know how to make it calculate the percentage for each payment for each day. For example, for 2019-04-01 there were 20 payments so, the percentage of successful payments = 50%, failed = 25% and refunded = 25%.

How can I make it return this?

2019-04-01,  successful,  10, 50%
2019-04-01,  failed,       5, 25%
2019-04-01,  refunded,     5, 25%
2019-04-02,  successful,  14, 58.3%
2019-04-02,  failed,       4, 16.6%
2019-04-02,  refunded,     6, 25%

My problem is how to make it count the total number of payments on each day so I can calculate the percentage?

Thanks in advance!


texts: SQL Server Script to replicate recent inserted data to subscriber and to remove same data from publisher

texts:
I want to replicate newly added data from publisher DB to subscriber DB.
When newly added data is replicated in subscriber dB the same data will be removed from publisher DB.
How to do this?


texts: So, it's been a while since the discussions about patching changes to SQL Server by Microsoft in these links (WSUS for SQL server patching & https://www.brentozar.com/archive/2016/08/windows-update-now-delivers-sql-server-cumulative-updates/#respond) ... I wondering if anyone has  any more insight to using WSUS to patch our companies SQL Servers using this approach ... Anyone using WSUS successfully to patch SQL Server 2012 and beyond? It seems like this is the direction that Microsoft is heading ... If we were to do this (and we have tested a couple of servers), it would be to enable the SQL Server patches along with the Windows patches. Any thoughts?


texts: How do I do an ODBC connection to ORACLE from SSIS/ Visual studio?

texts:
Despite that OLE Db and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS.

So --- I'm trying to set up a simple ODBC connection to an Oracle DB in SSIS -- to no avail.

The OLDE db/ ADO.Net connections were easy. I entered the credentials and boom, connected.

ODBC proves very difficult. I'm running Windows 10 64 bit. Visual Studio 2015 (I believe the designer for SSIS was only meant to initially play with 32 bit ODBC connections). Oracle is version 12c.

So ... I did the convoluted Oracle driver install. Downloaded Oracle basic light express + ODBC. Put them in the same directory. Ran odbc.install.exe WITH admin privledges. Finally got the Oracle driver to show up in Microsft ODBC (32 or 64, ended up downloading both eventually). I test the connection in Microsoft ODBC setup? Success!! NOOOOW. .. enter Visual studio. Set up connection --- test --- FAIL! Some cryptic error .... 127 ... the "oracle" in quora32.dll can't be found.

Sigh ... It's like learning Mandarin Chinese fluently is easier than simply trying to set up an Oracle ODBC connection in SSIS. Anyone know what to do here? Are Microsoft Drivers for Oracle still available for download?


texts: Automating MySQL DB Creation with Specific date

texts:
Here i come again with another challenge . Basically i'm looking forward for the best way to have a windows based script to create a MySQLDB, or multiple MySQLDB, once a week having the following format : YYMMDD_HHMM_DBNAME.

For e.g , every saturday or sunday, the script will be running to create the DB having the previous Thursday date. The goal is then to restore a dump into that new DB.

If anybody have an idea on how this can be done, i will really appreciate.

With thks.

Rgds


texts: Query is slow on AlwaysOn Secondary node

texts:
I have two identical servers (same hardware, cpu, memory etc) in an AlwaysOn cluster (sync commit and read-intent enabled), both servers running with SQL Server 2016 SP1 (13.0.4001.0).
For some of the queries with identical query plans and statistics, the queries are taking over 10secs to run on the secondary replica where as they finish under 2 secs on the primary replica. I have verified the statistics and query plans are exactly the same and tested executing the queries with no other load on these servers. Not able to find the reason why queries are slow on secondary replica.

Query and Plan from primary replica
https://www.brentozar.com/pastetheplan/?id=S1O5Yxscb

Query and Plan from secondary replica
https://www.brentozar.com/pastetheplan/?id=SJdz5ljq-

However, when I give a maxdop = 8 hint in the query on the secondary server, the n it finishes in 2secs.

Also, when I switch roles upon failover, the query executes in 2 secs on the new primary server and on the secondary server it again takes around 10secs. So doesn't seem to be a hardware problem as the query executes fast on the server which ever is designated as primary on the AG cluster and it runs slow on the secondary node.


texts: How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

texts:
I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights).

There are no other sysadmin accounts for the instance except SA.

I was set up for just Windows Authentication so I hacked the LoginMode in the registry to 2 so I could login as SA using Sql Authentication. This does indeed set the login mode to Mixed, however the SA user is disabled by default and I can't reenable it because I am don't have sysadmin rights.

How do I enable the SA login so I can get in and reassign the sysadmin to my normal account? Is there a registry setting for this too or is it stored in the master database?


texts: SQL Server 2008 - Replacing csv substrings in view-row using data from other table

texts:
Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and STRING_AGG is not available in 2008 version.
EDIT:
Added create and insert script
CREATE TABLE Data(
Id int,
Value1 varchar(50) NULL,
Value2 int NULL,
Value3 datetime
)
GO

CREATE TABLE CodeValue(
    Id int,
    Code varchar(50) NULL
)
GO

INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (1, N'0;1;2', 43, CAST(N'2020-07-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (2, N'0;2;3', 652, CAST(N'2020-07-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (3, N'2', 1234, CAST(N'2020-07-02T00:00:00.000' AS DateTime))
GO

INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (0, N'Apple')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (1, N'Orange')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (2, N'Banana')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (3, N'Dogmeat')
GO

Consider my view contains data from two tables; Data and CodeValue, that would look like this:
Data
Id | Value | Value2 | Value 3
==============================
  1| 0;1;2| some other data
  2| 0;2;3|
  3| 2    |

CodeValue
Id | Code
=============
  0| Apple
  1| Orange
  2| Banana
  3| Dogmeat

So the actual output from the SELECT query in my view would be:
View
Id | Value
============
  1| Apple, Orange, Banana
  2| Apple, Banana, Dogmeat
  3| Banana

I've messed around with stored procedures and functions, but can't wrap my head around those and how to actually implement this.
EDIT 2:
Tried using STUFF() using the following template:
WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

However I can't seem to join codeValues on split values using homebrew split_string function:
CREATE FUNCTION dbo.tvf_SplitString (@stringToSplit VARCHAR(100))
RETURNS @returnList TABLE(Id VARCHAR(5))
AS
BEGIN
    DECLARE @splitValue VARCHAR(5)
    DECLARE @post INT

    WHILE CHARINDEX(';', @stringToSplit) > 0
    BEGIN
        SELECT @pos = CHARINDEX(';', @stringToSplit)
        SELECT @splitValue = SUBSTRING(@stringToSplit, 1, @pos-1)
        INSERT INTO @returnList
        SELECT @splitValue
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit -@pos)
    END
    INSERT INTO @returnList
    SELECT @stringToSplit
    RETURN
END


texts: MySQL slave not able to execute long running update query

texts:
I am running a Master-slave replication servers of MySQL. As part of clean up of old data, i ran a delete query which deletes huge number of records from the database. It ran fine on Master server but on slave server, it is giving me following error:

  Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

The slave server machine is less powerful than master one. How can I get past this?

The query is single line delete query. I am running MySQL 5.6.


texts: Determining the optimal Column Widths from a .csv File

texts: I want to import a .CSV file into a MySQL Table.  I would like to determine the optimal column widths to use based on the actual data contained in the CSV file.  Are there any scripts or utilities to accomplish this?


texts:
I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB data.

I'm a complete newbie, but if I understand correctly PG has (at least) 2 pools of buffers, one for indexes and one for "heap" (data).

I imagine that my images 'pollute' the heap cache, making virtually nothing else cached (the cache obviously is not large enough to accommodate all the images).

Can I stop the image blocks only (I don't mind the image indexes being cached) from being cached?


texts: How to recover data from corrupted SQL Server database?

texts:
We had several power outages and server rebooted couple times which seemed to cause issues with one of the databases.  We tried detaching at attaching database again but it looks like database is corrupted and we’re getting the

Msg 5172, Level 16, State 15, Line 1

Claiming that database header is invalid. Is there anything we can to do repair database or extract data from MDF file?  We do have a backup but it’s like 2 weeks old and doesn’t contain all data.


texts: Inserting empty string on Windows MySQL doesn't use default value

texts:
Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

The query is:

insert into test2 (id,test) values('','');

If I use this query on MYSQL/LINUX, it runs just fine:

mysql>  insert into test2 (id,test) values('','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql>

id is autoincrement
test column is INT and has default value 0

Thanks.

SOLUTION:
on my windows machine i disable "strict" mode
in my.ini file, i removed STRICT_TRANS_TABLES from sql-mode=


texts: How to Combine Insert, Update, and Delete in the same `Instead Of` Trigger, in Oracle db?

texts:
When creating an Instead Of trigger, Sql Developer allows to include all 3 DML actions in the same trigger:

The result SQL looks like:

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF DELETE OR INSERT OR UPDATE ON PO_COSTED_BOM_V
BEGIN
  NULL;
END;

I'm able to combine Insert and Update like this:

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF INSERT OR UPDATE OR DELETE ON PO_COSTED_BOM_V
BEGIN
  UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;
  IF SQL%NOTFOUND THEN
      INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE)
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);
  END IF;
END;

But how do i get the Delete statement into the trigger?
How does the trigger know which DML action was executed?

thx


texts: Datalink error when executing remote query

texts:
This afternoon i was working with an old datalink defined to transfer data from one database to other.

It's the first time i've worked with this 'trick'. We use an specific user to work with the database link (user TRANSF). My questions :

1) I know the password of the TRANSF user. But when i define/create the database link i can't put the password in clear. I obtained an error message and the database links were not created. I put the encrypted password which i obtained from the dab_users table, password column. Is this correct ? I cannot put the password in clear in the database link ? Why ?

2) After creating the database link with the encrypted password, i tried to execute a simple query which looks to the other database, obtaining this annoying error message.

Query: SELECT COUNT(*) FROM CPLL35.BKUPDATA@PLACEX

Database Link:

CREATE DATABASE LINK "PLACEX"
   CONNECT TO "TRANSF" IDENTIFIED BY VALUES 'xxxxxx'
   USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = myserver.ush.com)(PORT = 1521)))(CONNECT_DATA = (SID = SPAPA) (SERVER = DEDICATED)))';

Error Message:

SQL> 35127530 : map : Permission Denied
SELECT COUNT(*) FROM CPLL35.BKUPDATA@PLACEX
                                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

UPDATE:

Following the blog entry that Phil posted in the first commentaire. Now my problem is how to obtained the password of the TRANSF user. It's clear than password picked for dba_users table didn't match with expected password. In the blog entry the user's password which works is longer than than previous password definition.


texts: How to connect to a database in my vps from remote machine?

texts:
I have installed MySQL on my VPS IP, and issued the following command:

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

service mysql restart

Now I want to log in to MySQL on my VPS IP from a remote machine.

mysql -h vpsip -P 3306 -u root -ppasswd

ERROR 2003 (HY000): Can't connect to MySQL server on 'vpsip' (111)

Why can't log in? How to connect to MySQL from a remote machine?


texts:
I was doing some investigation on how other DBAs go about performing automated regular tests of their database backups to ensure recoverability in case of disaster and I'm quite disappointed at what sparse topics I found on the topic.
I'll try to describe the solution that I came up with for my company in the comments, but how do you guys ensure that your backups are actually recoverable?

Do you recover your backups to test servers on regular basis? Using scripts?
Third-party software with this capability?
Running verify-only on the backups?
Letting your junior DBA do recovery tests?

Let me know if I should rephrase or add anything to the question.

UPDATE

After some time and polishing, i decided to publish my script if anyone's interested. https://github.com/curiebabz/SSARS


texts:
I was trying to answer this question stating from clause is optional..But i am stuck with an Operator in plan..below is screenshot of execution plan ..

As you can see there is BMK operator in query plan ,but no indication showing how it is calculated..

steps i have tried so far:
I started searching with BMK1000 ,but it shows a bunch of questions with the same operator.finally i found one thread which says "The BMK that you're referencing is the storage location within the heap that gets kept with the nonclustered index instead of the cluster key."..But not sure how this is related to me,since i don't have any indexes..

ASK:
what is the BMK operator and how it is calculated.Any Pointers also will be helpfull

Here is SQLFiddle to Repro the issue


texts: How to count references to fields given the definitions of views?

texts:
I have a bootstrapping script which defines views to be exposed to the data users in public schema.
It filters out sensitive fields from the tables in private schema, also makes various aggregations.

I wish to understand which fields (in tables) are referenced the most, especially in JOIN, WHERE, ORDER BY, GROUP BY and similar statements (which would indicate that the field should be indexed).

The problem is that the references are nested and aliased so simple(-ish) regular expressions don't seem to be enough.

For example:

CREATE VIEW public.user AS (
  SELECT name FROM private.user
);

CREATE VIEW public.device AS (
  SELECT user_name, type FROM private.device
);

CREATE VIEW public.user_size AS (
  SELECT name, count(*)
  FROM public.user u
  JOIN public.device d ON u.name = d.user_name
  GROUP BY name
);

would ideally detect that private.user.name is referenced 2 times (once in JOIN and once in GROUP BY) and private.device.user_name is referenced 1 time (in JOIN).

Are there any means to help automate this process? DB is PostgreSQL, if that makes a difference.


texts:
Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results.

In an attempt to not use OFFSET, I pass the id from the last row in the previous page in a parameter called p (prevId). I then select the first three rows whose id is higher than the number passed in the p parameter. (as described in this article)

For example, if the id for the last row in the previous page was 5, I'd select the first 3 rows with an id is higher than 5:

SELECT
  id,
  firstname,
  lastname
FROM
  people
WHERE
  firstname = 'John'
  AND id > 5
ORDER BY
  ID ASC
LIMIT
  3;

This works great and the timing isn't very bad either:

Limit  (cost=0.00..3.37 rows=3 width=17) (actual time=0.046..0.117 rows=3 loops=1)
   ->  Seq Scan on people  (cost=0.00..4494.15 rows=4000 width=17) (actual time=0.044..0.114 rows=3 loops=1)
         Filter: ((id > 5) AND (firstname = 'John'::text))
         Rows Removed by Filter: 384
 Planning time: 0.148 ms
 Execution time: 0.147 ms

Although, if the user, on the other hand, would like to return to the previous page, things look a bit different:

First, I'd pass the id for the first row and then put minus sign in front of it to indicate that I should select the rows with an id that's less than (a positive) p parameter. Namely, if the id for the first row is 6, the p parameter would be -6. Similarly, my query would look like the following:

SELECT
  *
FROM
  (
    SELECT
      id,
      firstname,
      lastname
    FROM
      people
    WHERE
      firstname = 'John'
      AND id < 6
    ORDER BY
      id DESC
    LIMIT
      3
  ) as d
ORDER BY
  id ASC;

In the query above, I first select the last 3 rows with an id that's less than 6 and then reverse them in order to present them in the same way as the first query described in the beginning.

This works as it should, but since the database is going through almost all my rows, the performance is suffering:

Sort  (cost=4252.75..4252.76 rows=1 width=17) (actual time=194.464..194.464 rows=0 loops=1)
   Sort Key: people.id
   Sort Method: quicksort  Memory: 25kB
   ->  Limit  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.460..194.460 rows=0 loops=1)
         ->  Sort  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.459..194.459 rows=0 loops=1)
               Sort Key: people.id DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Gather  (cost=1000.00..4252.72 rows=1 width=17) (actual time=194.448..212.010 rows=0 loops=1)
                     Workers Planned: 1
                     Workers Launched: 1
                     ->  Parallel Seq Scan on people  (cost=0.00..3252.62 rows=1 width=17) (actual time=18.132..18.132 rows=0 loops=2)
                           Filter: ((id < 13) AND (firstname = 'John'::text))
                           Rows Removed by Filter: 100505
Planning time: 0.116 ms
Execution time: 212.057 ms

With this being said, I appreciate that you have taken the time to read this far and my question is, how can I make the pagination more efficient?


texts: Multiset operators on nested tables with non scalar types

texts:
I have a function, which performs multiset operation on two nested tables and returns result:

function odejmijEgz(tab typ_NT) return typ_NT is
  tab1 typ_NT := typ_NT();
  cursor c_osr_egz is select distinct o.id_osrodek oid, o.nazwa_o nazwa_o from egzaminy e
    right join osrodki o on e.id_osrodek = o.id_osrodek
    where e.id_osrodek is null;
  i number;
  begin
    for v_osr in c_osr_egz loop
      i := c_osr_egz%rowcount;
      tab1.extend;
      tab1(i) := v_osr;
    end loop;
  return tab multiset except tab1;
  end odejmijEgz;

where types are declared in pl/sql block:

type typ_Rec is record(Id osrodki.id_osrodek%type,
                        Nazwa osrodki.nazwa_o%type);
type typ_NT is table of typ_Rec;

Parameter of the function is nested table, populated with records.

When I use multiset except in function I got an error:

Error: PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'

But if I change it to multiset union it works. The tables are of the same type. Whats wrong with that?


texts: SQL Server Temporal Tables History Table size

texts:
I have a question regarding the under the hood parts of the History Table in the Temporal Tables feature of Microsoft's SQL Server.

To ask it I am going to create a hypothetical situation.  Say I have a table with 300 columns in it.  Each column has a char(10) in it.  (3,000 bytes).

I insert a row into the main table.

I then go an modify one of the columns with an update statement.  That causes the modified row to be inserted into the history table.

I then change that same column 4 more times (5 modifications total.)

What I am wondering is if that means that the size of my history table is now 3,000 x 5 = 15,000 bytes?

Or does it do some kind of compression under the hood to realize that most of the data did not change and does not need to be stored each time?


texts: MySQL Db design: Optional data in separate table or as NULL values in original table

texts:
I am having following problem:

User fills out the form, which consists of two sets of fields - header (basic information about biological observation) and detail (measurements and advanced information). It is assumed, that not every user will fill out the detailed information.

I am wondering, what is better design with respect to performance

to have one table with all fields and those detail fields having NULL values
to have two separate tables - one for header and one from detail with 1 to 1 relation, and in case the user did not fill the detail, do not insert record into detail table.

Further information: header and detail will always be used together, so for every SELECT statement, I would need to join them.

I suppose I could formulate the question as follows: In case of two-tables design, would the space I save make up for the slower performance (due to join)?

I am using MySQL database.


texts: How to rebind a package inside a Stored Procedure (DB2)

texts:
I have a few Stored Procedures for my application (DB2 V 9.7.3). I need to rebind the packages corresponding to the Stored Procedures. I wish to get having them recompiled inside a Stored Procedure by retrieving the package names via a Cursor and rebind the packages one by one.

The command to do is

REBIND PACKAGE <schema>.<pkgName>

To execute this command, I tried the following options but both of them failed.

EXECUTE IMMEDIATE <stmt>;

CALL SYSPROC.ADMIN_CMD(<stmt>);

Is there a way to get this accomplished? Any help would be highly appreciated.


texts: SQL Server Connection property applicationIntent

texts:
I have a doubt in ApplicationIntent connection property of SQL Server.
The doc say it has two values ReadWrite and ReadOnly. If I set the value as ReadOnly, will it allow only read queries or will it also support update queries to SQLServer?

I am asking this ,keeping read only routing in AvailabilityGroups of SQLServer in mind!


texts: Table design for linking pictures to a place

texts:
Could someone give me some advice on designing a table for a system that has to store
x picture for 1 place?

Is it better to have something like this:

place table:

id_place / reference
100           1
200           2
300           3

picture table:
id_picture / reference / picture_url / main_piture
1               1            xxx           false
2               1            xxx           true
3               1            xxx           false
4               2            xxx           false
5               2            xxx           true
6               3            xxx           false
7               3            xxx           true
8               3            xxx           false

Or is it better to have it all in one table?

picture table:
id_picture / id_place / picture_url / main_picture


texts: physical backup in mariadb 10.5 InnoDB: Unsupported redo log format

texts:
I have mariadb 10.5 version 3 node galera cluster, on node 1 physical backup is running/working but on node 2 and 3 physical backup script is not working and getting error.....
mariabackup --backup --target-dir=/var/mariadb/backup --user=root --password=xxxx@123
error
2021-03-22  8:29:28 0 [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.5.8.
[00] 2021-03-22 08:29:28 Error: cannot read redo log header
Can you please somebody suggest here what is going to wrong.


texts: MySQL queries still slow after deleting bunch of records

texts:
I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of table locks. I am a developer but no mysql/database expert. My host suggested that I either delete and/or change the two tables in question to innoDB. So as these tables had a lot of junk data I decided to delete a bunch of records. I would say that these two tables are about 25% of the size when this problem started. Problem is that, it still has not made any difference. So my questions:

Do I need to clear cache or optimize table in order to see the
effects? My host is still suggesting I change those tables to innoDB
which is fine but I'm not sure why deleting that many records have
not made a difference.
Also I have read that it is better to
recreate the table than just optimizing? If needed I can hire a
database admin to help me but I would like to at least try a few
things if this is something simple. Can someone please guide me
through this.

One more important thing to add, it is a legacy website running on php 5.4 and mysql 5.6


texts: What's the most efficient UUID column type

texts:
For storing a 128 bits UUID there are multiple storage options:

a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if db supports it

From an indexing point of view which of those are the most efficient? If the db doesn't support a dedicated uuid type which of 1, 2, 3 are the best candidates?


texts: How to re-enable read ahead (prefetch) feature in SQL Server after disabling it?

texts:
In Microsoft SQL Server, I've disabled the read ahead (prefetch) feature using

    DBCC TRACEON(652,-1)

But does anyone know how to enable the read ahead feature?

Before I disabled the read ahead, using

    set statistics io on

I get this result:

    Scan count 1, logical reads 529, physical reads 4, read-ahead reads 1192

Now after disabling read ahead using the above DBCC query, I get this:

    Scan count 1, logical reads 44, physical reads 19, read-ahead reads 0

I'd like to be able to disable/enable the prefetch feature freely so I could compare among different queries. I googled everywhere but didn't find an answer for that... Anyone knows how to re-enable it? Thanks!


texts: Can't create federated table in MariaDB using CREATE SERVER - 'Foreign data src error'

texts:
I'm using MaraiaDB 5.5.44 on Centos 7.2.

I've used federated tables before across physical servers ... I found the 'CREATE SERVER' mechanism a bit flaky (I could only manage to link tables of the same name) but it worked.  In this case I want to set up federated tables in database A that reference tables in database B where both databases on on the same host.  I'd like the connection between the tables to work using the Unix socket rather than TCP/IP.  The MariaDB server isn't bound to any network interface.

It all works using the CONNECTION string, creating a link here from table 'tmp_table' on the local database to the table of the same name in the 'tmp' database:

> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='mysql://testuser:testpassword@localhost/tmp/tmp_table';

That works perfectly.  But when I set up a SERVER for the connection, using the CREATE SERVER statement, I get a 'Foreign data src error':

> create server test_server foreign data wrapper mysql options (user 'testuser', password 'testpassword', host 'localhost', database 'tmp');

> select * from mysql.servers;
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| Server_name      | Host      | Db                   | Username       | Password         | Port | Socket | Wrapper | Owner |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| test_server      | localhost | tmp                  | testuser       | testpassword     |    0 |        | mysql   |       |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+

> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='test_server/tmp_table';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  database: 'tmp'  username: 'testuser'  hostname: 'localhost'

I've tried various permutations of the CONNECTION string - 'test_server/', 'test_server' - but always get the 'Foreign data src error'.

I have been able to get federated tables working before, using a TCP/IP connection to the source database on another host, so I'm wondering if I've made a mistake or if MariaDB simply can't use a SERVER for a connection between local databases over the Unix socket.  Can anyone help me with this problem?


texts: Product attribute schema design, mixing solutions

texts:
I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) used in this situation, but none of them looks promising. I am thinking about mixing them somehow.

Lets say, I have these product types (D - dimension, but could be attribute or entity...):

Product_type1: D1 - int, D2 - int, D3 - string, D4 - date, D5 - string

Product_type2: D1 - int, D2 - string, D3 - date, D4 - int, D5 - string, D6 - int

Solution 1:

I store a fix number of attributes of every datatype for a product, let's say 3 int field, 2 date, 2 string. The most important attributes stored here (important regarding indexing and searching). The rest of the attributes are stored in a JSON field or an array format.

Solution 2:

Store everything in indexed JSON field and search in the table with 'LIKE' queries.

How fast is a Like query in MySQL when you have a big product database (20 000 - 30 000 products). Is solution 1 better or not?


texts: Efficient way on updating multiple tags or categories


texts: What's the best way to deal with a database with a big table with information that will RARELY be used?

texts:
I think useless is a heavy word for this.

We have a database with 500GB, and most of it's information is only there if we MAYBE need to use them. it's a big database with credit card information and until now, we've never had the need to select them ( the table has 500millions rows ).

this database is growing daily and I would like to know what is the best approach of this?

I can't think in something smart. I don't think a backup and delete all rows will be the best approach. then if we need those rows, we restore the backup.

Is there something like partitioning to be used here?
there are 2 tables with this "problem". how can I get rid of these 2 tables, but maintaining these tables secured?


texts: Converting Cursor based filtering logic to CTE based

texts:
I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition where I have to get the top 10 values for each iteration in cursor which I am not able to do with set based logic.

Assuming the cursor based logic being:

SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

    FETCH NEXT FROM @mycurse INTO @SomeVariable

    IF @@FETCH_STATUS <> 0
        Print 'Error in the Cursor Fetch Statement'

    WHILE @@FETCH_STATUS = 0

    BEGIN

INSERT INTO @TOP10
    SELECT Value1,Value2,Value3,Value4
        FROM (
                select Value1,Value1,Value3,Value4 from BaseTable where SomeVariable=@SomeVariable
        )

        FETCH NEXT FROM @mycurse INTO @SomeVariable

    END

CLOSE @mycurse

And the CTE based soltion looks like this:

;With myCTE(value)
as
(
    SELECT value FROM sometable
)
INSERT INTO @TOP10
    SELECT Value1,Value2,Value3,Value4
    FROM
      (select
          Value1, Value1, Value3, Value4
       from BaseTable BT, myCTE MC
       where BT.SomeVariable = MC.SomeVariable)

As you see both work fine and return identical result, with CTE version using significantly less time. But the logic of having top 10 values makes it go wrong, it returns only 10 values where it is supposed to return 20 values.

SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

    FETCH NEXT FROM @mycurse INTO @SomeVariable

    IF @@FETCH_STATUS <> 0
        Print 'Error in the Cursor Fetch Statement'

    WHILE @@FETCH_STATUS = 0

    BEGIN

INSERT INTO @TOP10
    SELECT top 10 Value1,Value2,Value3,Value4
    FROM
        (select
             Value1, Value1, Value3, Value4
         from BaseTable
         where SomeVariable = @SomeVariable)

        FETCH NEXT FROM @CUR_Liability INTO @VarLiability

    END

CLOSE @CUR_Liability

So please provide me with a solution for this scenario, it does not necessarily need to be CTE based my goal is to reduce run time. Also I can update with sample result set if this is not clear enough. Thanks in advance.

Update : I have managed to cook up a solution using Row_Number() function but I am still curious about other options.


texts: Can Transactional Replication and triggers be used for near-realtime ETL

texts:
We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers?

How we currently ETL
We use sql server hosted on azure. We have a OLTP database for our application and keep a second sql server database as a data warehouse for reporting and modeling. Currently we orchestrate a nightly ETL (ELT really) process with azure data factory. The nightly process syncs reference tables on the data warehouse with the OLAP app database and calls heavy stored procedures to rebuild all the denormalized tables required for reporting and modeling. The nightly process takes a long time.

How we think it could work better in near real time
Proposal is to select the reference tables we want to sync from the OLTP database to the data warehouse and do the one-way sync with sql server's built in transactional replication. Then on the data warehouse we would put triggers for changes (IUD) to the reference tables. Those triggers would insert a modification record into a table on the warehouse which would act as a queue tracking changes to reference table data. We would then build a process to watch that queue. As new modification records came in, the process would see what table was changed and for what primary key and then go update all the denormalized tables that depend on that combination of reference table and primary key. We are unsure if we would write those denormalization procedures as stored procedures on sql server or as a separate .net application. Either way seems like an option.

Anyone with experience in those areas think that could work. Are we crazy?


texts:
Using Sql Server 2008R2.

I have a backup of a filegroup. Is it possible to restore this to a different file group (possibly even on a different database)?

I have tried

RESTORE DATABASE my_database
   FILEGROUP = 'newFileGroup'
   FROM DISK='C:\restore\backupOfFileGroup.bak'
   WITH RECOVERY

But get the following error:

Filegroup 'newFileGroup' cannot be restored because it does not exist in the backup set.


texts: Refresh Materialized View impact on the DB

texts:
Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (4M more or less) and we have created a materialized view with a subset of this table (2M aprox) changing some columns types to be more efficient in querying. Our pg_conf has not been changed, being RDS Postgres' default.

This is our view definition:

CREATE MATERIALIZED VIEW public.customers_mv as
SELECT
    id,
    gender,
    contact_info,
    location,
    social,
    categories,
    (social ->> 'follower_count')::integer AS social_follower_count,
    (social ->> 'following_count')::integer AS social_following_count,
    (social ->> 'peemv')::float AS social_emv,
    (social ->> 'engagement')::float AS social_engagement,
    (social ->> 'v')::boolean AS social_validated,
    search_vector,
    flags,
    to_tsvector('english',concat_ws(' ','aal0_'||(customers.location ->> 'aal0'),
      'aal1_'||(customers.location ->> 'aal1'),
      'aal2_'||(customers.location ->> 'aal2'),
      'frequent_location_aal0_'||(customers.location -> 'frequent_location' ->> 'aal0'),
      'frequent_location_aal1_'||(customers.location -> 'frequent_location' ->> 'aal1'),
      'frequent_location_aal2_'||(customers.location -> 'frequent_location' ->> 'aal2'),
      'last_post_location_aal0_'||(customers.location -> 'last_post_location' ->> 'aal0'),
      'last_post_location_aal1_'||(customers.location -> 'last_post_location' ->> 'aal1'),
      'last_post_location_aal2_'||(customers.location -> 'last_post_location' ->> 'aal2'),
      'admin_location_aal0_'||(customers.location -> 'admin_location' ->> 'aal0'),
      'bio_location_aal0_'||(customers.location -> 'bio_location' ->> 'aal0'))) as loc_vector
FROM public.customers
WHERE (customers.social -> 'follower_count') > '5000'
AND customers.social ? 'last_posts'
AND (customers.flags IS NULL OR NOT customers.flags @> '{"destroy": true}'::jsonb);

CREATE INDEX customers_mv_followerc_idx ON customers_mv USING BTREE (social_follower_count);
CREATE INDEX customers_mv_folling_idx ON customers_mv USING BTREE (social_following_count);
CREATE INDEX customers_mv_emv_idx ON customers_mv USING BTREE (social_emv);
CREATE INDEX customers_mv_gin_social_idx ON customers_mv USING GIN (social jsonb_path_ops);
CREATE INDEX customers_mv_partial_social_validated_idx ON customers_mv (social_validated) WHERE social_validated = FALSE;
CREATE INDEX customers_mv_categories_idx ON customers_mv USING gin (categories);
CREATE INDEX customers_mv_gin_location_idx ON customers_mv USING GIN (location jsonb_path_ops);
CREATE INDEX customers_mv_gin_loc_vector ON customers_mv USING gin(loc_vector);
CREATE UNIQUE INDEX customers_mv_uniq_id_idx ON customers_mv (id);

Our view has some columns just for accessing data like location or social (both jsonb type) and some other like loc_vector for querying faster.

Now the problem:
The problem occurs when we try to refresh, refresh concurrently or create a new materialized view, when we try to launch the refresh command the CPU or the write IOPS crash the DB.

Here we can se how hard it hits the DB

What would be the ideal setting given our requirements?

Its a problem of increasing infrastructure or maybe a matter of configuration/optimization?


texts: Creating optimized table without duplicates in MySQL

texts:
I am struggling to create a table with a structure that shows values of user-date relation.
Sample pseudo-database:

+------+----------+----------+----------+-----
| user | 01012016 | 02012016 | 03012016 | ...
+------+----------+----------+----------+-----
| 1    | 2        | 57       | ...      | ...
| 2    | 12       | 73793    | ...      | ...
| 3    | 56       | 468      | ...      | ...
| 4    | 1689     | 12357    | ...      | ...
| 5    | 946      | 13       | ...      | ...
+------+----------+----------+----------+-----

Values are integer.

According to MySQL Reference Manual, key range is limited.

What structure a table should have to allow unlimited amount of columns and rows in this case?

I am aware that I can create a separate table for each user, but the idea is to keep everything optimized - in a smallest possible number of tables.

I am also aware of a simple structure (user, date, value), but there will be a lot duplicates, to be specific, in 'user' and 'date' columns:

+------+----------+----------+
| user | date     | value    |
+------+----------+----------+
| 1    | 01012016 | 57       |
| 1    | 02012016 | 73793    |
| 1    | 03012016 | 468      |
| 2    | 01012016 | 12357    |
| 2    | 02012016 | 13       |
| 2    | 03012016 | 618      |
| ...  | ...      | ...      |
+------+----------+----------+

And even if I decide to use this structure, what key should I define a primary one?


texts: How to configure data collection in SQL Server 2012?

texts:
In SSMS of SQL Server 2012 Enterprise on Windows Server R2 Standard, each time I am trying to set sub-option "On demand" of option "Non-cached - Collect and upload data on the same schedule" of Data Collection Set, I am getting the error:

  A  collection set cannot start without a schedule. Specify a schedule for the collection set

  TITLE: Microsoft SQL Server Management Studio

  Operation 'Alter' on object 'Disk Usage' failed during execution.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Caught error#: 14684, Level: 16, State: 1, in Procedure:
  sp_syscollector_start_collection_set, Line: 203, with Message: Caught
  error#: 14693, Level: 16, State: 1, in Procedure:
  sp_syscollector_start_collection_set, Line: 108, with Message: A
  collection set cannot start without a schedule. Specify a schedule for
  the collection set. (Microsoft SQL Server, Error: 14684)

Now, I do not see how to re-setup or reconfigure the Data Collection in any way other than to enable and disable already existing malfunctioning one.

Any ideas how to make it working?

First of all, I'd like to understand whether it is me who screwed it (though I tried on different machines) or it is someone else ?

Update:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)


texts: MongoDB Insufficient free space for journal files

texts:
I have installed a standalone mongo 2.6 in amazon ec2. The instance has reach the limit of disk space and it doesnt run now. I have set before the smallfiles=true in mongod.conf file, but for some reason this doesn't work neither.
The log file says:

  2015-03-12T19:41:26.881+0000 [initandlisten] ERROR: Insufficient free space for journal files
  2015-03-12T19:41:26.881+0000 [initandlisten] Please make at least 422MB available in /data/journal or use --smallfiles
  2015-03-12T19:41:26.881+0000 [initandlisten] exception in initAndListen: 15926 Insufficient free space for journals, terminating

I tried to set journal path in another volume >

  sudo ln -s /journal /data/journal

But this doesn't work neither and logs the same error above :(

I want to mantain the /data (with all dbs files) in the same disk , I only want to move the journal path to another disk , is that possible?
Please , any help is welcome!

pd: I used before tune2fs for get more space , but now I don't have it.


texts: How to update a UID throughout the database?

texts:
Our database makes use of uniqueidentifier and we have the need to update that value everywhere it's found in our system.
Sample minimal structure:
CREATE TABLE Users
(
    id uniqueidentifier not null default newsequentialid() primary key,
    [name] nvarchar(100) not null
)

CREATE TABLE Tasks
(
    id uniqueidentifier not null default newsequentialid() primary key,
    [name] nvarchar(max) not null,
    userId uniqueidentifier not null foreign key references Users(id)
)

DECLARE @users TABLE(id uniqueidentifier)
DECLARE @uid uniqueidentifier

INSERT INTO Users(name) OUTPUT INSERTED.id INTO @users VALUES('Jim Bob')
SET @uid = (SELECT TOP 1 id FROM @users)
INSERT INTO Tasks(name, userId) VALUES('Some task', @uid)

P.S. Obviously I have many more tables
I know how to search all database tables for a given value (I've used queries similar what's written here this before). I'm unaware of a simpler solution for uid's if any exist.
With the above, is there a simpler way than looping over tables, then columns of datatype uniqueidentifier, then locating the uid created in order to update its value to say 0x0?


texts:
There are two tables associated with each one to many in a field.
In Table 2, there are fields that duplicate the value in Table 1.

Is there a cheaper way to ensure (guarantee) the correspondence fields except FK-type (id, x, y, z) in unique index in Table 1?

Creation of an index of type (id, x, y, z) and one FK (there certainly can be replaced by FK, but FK on one field looks easier) is very expensive because the tables are very large.


texts:
I have moved the model database by using following method

For each file to be moved, run the following statement.

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev , FILENAME = 'G:\model\model.mdf' )
ALTER DATABASE model
MODIFY FILE ( NAME = modellog , FILENAME = 'G:\model\modellog.ldf' )

Stop the instance of SQL Server to perform maintenance.
Move the file or files to the new location.
Restart the instance of SQL Server or the server.

While restarting instance of server i am getting following error as shown below.

"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."

How to solve this error?


texts: MySQL dynamically optimize innodb tables without "file per table" setting

texts:
We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking websites offline.  I am thinking because tables are locked connections get backed up, or something like that. So I am thinking it’s the procedure that is the cause of this error and am looking at ways to refactor it to be smarter as to which tables it optimizes and/or chop up task to be run over many days.

This procedure only filters which tables and data bases to run the optimize command on by using LIKE against the table or database name. So, for every table match, “optimize table {tablename}” is run. These tables are both of MyISAM and InnoDB engine types.

One refactor approach would be to only optimize if the table really needs it.  If a fragmented table signifies it needs optimizing then finding out if is fragmented is not too difficult if,  if the table is a MyISAM, or InnoDB using innodb_file_per_table, where you can do the math on the information_schema fields “Data_free”, data_length and index_length.

Unfortunately the MySQL server is not using the “file per table” setting. This makes the “data_free” field seemly useless for this task because every InnoDB table with have the same value i.e. the free space in the ibdata file.

Maybe if I can understand answers to these questions I will better understand the solution i need.

Q 1: How much fragmentation is allowed before it affects performance?
Q 2: Should InnoDB tables even be optimized (some say yes others say no)?
Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?
Q 4: Is fragmentation the only reason to run “optimize table”?
Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE
Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?


texts: Five tables or a single table with an identifier column?

texts:
Do I go with:
Multiple Tables Method
   table5up             table10up                 table15up                 table20up             table25up
[id   -   name] [id   -   name   -   age] [id   -   name   -   age] [id   -   name   -   age] [id   -   name ]
[1    -   jhon] [1    -   mike   -   14 ] [1    -   alex   -   17 ] [1    -   mark   -   22 ] [1    -   felix]

Or
Single Table Method
[id  -  name  -  age   - up ]
[1   -  john  -  NULL  - 5  ]
[1   -  mike  -  14    - 10 ]
[1   -  alex  -  17    - 15 ]
[1   -  mark  -  22    - 20 ]
[1   -  felix -  NULL  - 25 ]

Knowing that I may have NULL values, which scheme, using 5 separate queries for multiple scheme:
SELECT id, name FROM table5up
SELECT id, name, age FROM table10up
SELECT id, name, age FROM table15up
SELECT id, name, age FROM table20up
SELECT id, name FROM table25up

Or one query defined by up column value for single scheme:
SELECT id, name, age FROM tableups WHERE up > 4 AND up < 26

I will not call all tables all the time, only one per time as example for multiple tables:
SELECT id, name FROM table5up;

and for single-table:
SELECT id, name, age FROM tableups WHERE up = 5

Props:
id INT(11), name VARCHAR(48), age TINYINT(3)