texts: MySQL Backups - Full vs incremental - innobackupex

texts:
So I'm implementing a backup strategy for our project and came across some text which I need absolute clarification, false positives are the death nell of IT.

I am looking to do your typical backup strategy, weekly full back-up with daily incremental backups. Here's the rub:

According to the docs, when doing a full backup you have to 'prep' it, which is to run all uncommitted transactions etc. You can prep the backup by using the innobackupex --apply-log /path/to/BACKUP-DIR command.

But according to these docs, if you do an incremental backup, you have to 'prep' it differently, using the innobackupex --apply-log --redo-only BASE-DIR command.

My plan is to write a cronjob to do a full backup on Sunday @ 12:01am, and an incremental on M-Sa @ 12:01 am. So my question is, should the cronjob prep every backup with the --redo-only flag, to ensure that full backup can be merged with following incremental backups?


texts:
INSERT INTO TARGET_TBL (KPIName ,KPILabel)
SELECT  `d`.`si_kpi_name` AS `KPIName`,
    IF(`t`.`kpi_val` = 0,
        WHEN (`v`.`TOIOS` >= `t`.`kpi1`) THEN 'Bad'
        WHEN (`v`.`TOIOS` >= `t`.`kpi2`) THEN 'Poor'
        WHEN (`v`.`TOIOS` >= `t`.`kpi3`) THEN 'Fair'
        ELSE 'GOOD',

        WHEN (`v`.`TOIOS` <= `t`.`kpi1`) THEN 'Bad'
        WHEN (`v`.`TOIOS` <= `t`.`kpi2`) THEN 'Poor'
        WHEN (`v`.`TOIOS` <= `t`.`kpi3`) THEN 'Fair'
        ELSE 'GOOD' )

        AS `KPILabel`
FROM (
    `voom_min` `v` LEFT OUTER JOIN `denver_config` `d` ON (`v`.`si_id` = `d`.`si_id`)
    LEFT OUTER JOIN gen_config g ON (d.si_name = g.si_name)
    JOIN `kpi_downstream` `t` ON ((`t`.`kpi` = 'market analysis'))
    )
WHERE (`v`.`TOIOS` IS NOT NULL)

MYSQL - Please concentrate on the part of where KPILABEL is calculated. What I am trying to do is:

If T.KPI_VALUE == O, Check set of conditions ... If not check set of conditions ...

I see in some websites the syntax of IF while using in insert query as

if(condition, if condition yes statements, ifnot statments)

I used this syntax for if syntax ...
for "if condition yes statements I used CASE statements"

Please help me to fix the syntax.


texts:
I would like to group by email. Sometimes email is missing in records, which then should not be grouped.
How can I create an exception for empty strings when grouping?
CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);
INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

As a result, I would like:
SELECT fullname, email FROM example GROUP BY email [...];
+----------+-------------------+
| fullname | email             |
+----------+-------------------+
| Name 2   |                   |
| Name 4   |                   |
| Name 1   | name1@example.com |
| Name 3   | name3@example.com |
+----------+-------------------+


texts: How to remove these duplicate records in an Oracle Database 12c?

texts:
I have these two tables:

messages(id primary key, message_date, created_at, ...)
user_messages(user_id, message_id references messages(id))

I have some duplicate rows in messages:

select user_id, message_date, count(*)
from messages inner join user_messages
on messages.id = user_messages_message_id
group by user_id, message_date;

user_id | message_date | count(*)
1       | 2019-01-01   | 2
1       | 2019-02-01   | 3
1       | 2019-03-01   | 2

How can I remove such duplicates, only retaining one of them, for example the one that its created_at (not message_date) is the minimum?


texts: What is the best way to store product size and color for an Ecommerce database?

texts: I am trying to find the best way to store product size, colour for an sports store website, something similar to what amazon does, where you can choose a shoe colour and number. The problem is that a product may be a shoe shirt ball bag etc, so the sizing charts/systems to be used will all be different for different types of products. Also one product may have different sizes and different colours and some colours may not have a certain sizes or vice-versa? I am a rookie in database designing so I don't know how to approach this problem.


texts: Create two columns from one, based on two different WHERE clauses

texts:
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_DataSets]
WHERE [VariableID] in ('1')
UNION ALL
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_oDataSets]
WHERE [VariableID] in ('4')

The above query produces 2000 rows; 1000 with Value for VariableID = 1, and 1000 with Value for VariableID = 2. What I want is 1000 rows, with one column (val1) containing the values for VariableID = 1, and the other column (val2) with the values for VariableID = 2.


texts: Oracle dump to csv and keep NULL as empty string

texts:
I have the following sql file:

SET COLSEP ';'
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET PAGESIZE 32766 -- Maximum number we can set.
                   -- i.e there will be duplication of headers.
SET LINESIZE 32766 -- Maximum number we can set.SET NEWPAGE NONE
SET VERIFY OFF
SET TERM OFF
--SET TRIMS ON
SET TRIMSPOOL ON
SET UNDERLINE OFF

SPOOL dump_audit_table.csv

SELECT timestamp, PROXY_SESSIONID,sessionid FROM dba_audit_trail where rownum < 10;

set trimspool off
SPOOL OFF
EXIT

Which exports the following CSV:

TIMESTAMP          ;PROXY_SESSIONID; SESSIONID
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228

My Problem is: PROXY_SESSIONID nulls are represented as '         ' a string with column width of spaces.
I could not find a way to completely terminate this. I would like the file to be exported in  the following way:

 04-29-2014 21:46:46;;707452228

e.g: NULL should be ;; read: empty string!
     SESSIONID should not have a leading space.


texts: How do I add a user that has read access to only ONE database?

texts:
I have two databases admin and testdb ; Added a user Alice in the test database

db.addUser( { user: "Alice",pwd: "Moon1234",roles: [ "readWrite" ] } );

But when I login using the user Alice, I can run "use admin" and run all read queries on that database.

Then I tried doing this:

db.addUser( { user: "Alice",userSource: "testdb",roles: [ "readWrite" ] } )

I get this error:

uncaught exception: couldn't add user: 'testdb' is not a valid value for the userSource field in testdb.system.users entries

Mongodb documentation is not very clear about it.

Basically my use case is that Alice should be able to read write ONLY in the "testdb"


texts: Performance of updating row if content has changed

texts:
I'm trying to make a website where there is a form that contains input type fields that are editable. In fact I get the elements from one of my tables (containing an id column and a string column) from my database, which I display via input with id as the id in this database and string as the string in this database. The user can edit the text in these fields and also there is the possibility to add a new 'row' (input with string, but which refers to no row in the database).

Finally, there is a button to save.

My question is do I directly modify the existing lines even if the user has not changed the string or should I check if the string has been updated? Which method is best in terms of performance?


texts: Stored procedure support in MySQL Workbench?

texts:
I've created a lot of stored procedures when I was using HeidiSQL on Windows.  I am now on an iMAC (x64) and it doesn't look like installing HeidiSQL is an option.
I've got MySQL Workbench version 8.0.24, it does list the stored procedures and functions but I cannot edit these in the application.
I'm surprised that this functionality appears to be missing or disabled.  Has anyone got a suggestions?  Tools that will work on iMAC (x64):
macOS Big Sur
Version 11.3.1
iMAC (Retina 5K, 27-inch, Late 2015)
Processor 4 GHz Quad-Core Intel Core i7
Memory 16 GB 1867 MHz DDR3


texts: How to create tables with geometry in Postgres?

texts:
I need to create 2 tables with distinct geometry.

One needs to be a table that has point information. The other needs to be a table that has line information.

They do not need to be populated as this is just a test. They just need to have the geometry column.

The code so far is:

CREATE TABLE point

CREATE TABLE line

How to add the geometry into the code?


texts: MySQL: Keep the 10 most recent rows for each user

texts:
I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way):

userid   : the id of the user that got the score
timestamp: the time the score was gotten
score    : the score itself

The goal of the query is to delete rows so we have at most 10 scores per user.We also want to keep the most recent rows. So we need to delete the older ones until we get to 10.Note that some players may have less than 10 scores.

How would I manage to do this?


texts: Does postgres optimise repeated expressions?

texts:
select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1

for this query, is calculation of :input <-> cube(0) repeated for every row?
Also, for
select calculation(row) from table_1 row where condition(row, calculation(row))

is calculation repeated in both the select, and where?


texts: Safest way to perform mysqldump on a live system with active reads and writes?

texts:
I'm not sure if this is true but I remember reading if you run the following command in linux

mysqldump -u username -p database_name > backup_db.sql

while reads and writes are being made to a database then the dump may contain errors.

Are there particular options in the command mysqldump to make sure this is done safely on a live system? I'm okay with reads/writes being disabled for our users for a few seconds (The database <50MB)


texts:
Whats wrong with the following query. The first part of the select always return 0 rows.

 DECLARE
    @AgentID varchar(20),
    @StringValues varchar(max),
    @ContactKey int,
    @Note varchar(255)

    SET @AgentID = 'c110010'
    SET @StringValues  = '08-328222,08-337333,09-348444,09-352555'
    SET @ContactKey = 0
    SET @Note = ''

     SELECT   @AgentID as agentid,mlsnum,'New' as status,ADDRESS,@Note as notes,PROPERTY_TYPE as propertytype FROM mls_unified_svo_tbl (nolock)
                    WHERE mlsnum IN (Select txt From [dbo].[fn_ListToTable](@StringValues, ','))
                    AND mlsnum  NOT IN (select mlsnum from listing_cart (nolock) where agentid = @AgentID and contact_key = @ContactKey)

 UNION ALL
    SELECT   @AgentID,mlsnum,'Old',ADDRESS,'','' FROM mls_unified_svo_tbl (nolock)
                    WHERE mlsnum IN (Select txt From [dbo].[fn_ListToTable](@StringValues, ','))
                    AND mlsnum  IN (select mlsnum from listing_cart (nolock) where agentid = @AgentID and contact_key = @ContactKey)


texts:
When reviewing the space used by datafiles in SQL Server, there is a large difference betweens what SQL reports and Windows.

How can this be explained?


texts: Outer Joins to find records with no matching associated entries

texts:
I'm trying to find all foo records which do not have any bar records that match a given criteria.

create table foo (
  id integer
);

create table bar (
  foo_id integer,
  name varchar(255)
);

insert into foo (id) values (1),(2),(3),(4),(5),(6);
insert into bar (foo_id, name) values
 (1,"name1"),
 (2,"name1"),
 (3,"asd"),
 (4,"name2"),
 (5,"name2"),
 (3,"name1");

Based on this .. only record 6 matches the criteria since its not tagged with name1 or name2.

select
  foo.*
from foo
  left outer join bar on (foo.id = bar.foo_id)
where
  bar.name not in ("name1","name2") or bar.name is null;

What am I missing?


texts: How to design a table for reconciliation

texts:
A Bank B sends a set of transactions to Bank C every few minutes. Same transactions could be sent multiple times (Bank C should recognize those transactions and should not process them).

At the end of the day, Bank B sends a reconciliation file (that SHOULD contain all transactions that SHOULD BE processed during the day) to Bank C.

Reconciliation process should provide a report with missing information for set of transaction during the day comparing with the set at the end of the day. Beside that, if any missing transaction is found, it should be processed.

Should the system use one table for transactions and another one for reconciliation or one single table for both information since the structure of data is the same?


texts: Can I restrict an SQL account from system tables / functions?

texts:
I've got a piece of vendor software that comes with a MS-SQL database. The software has a user account configured to access the MS-SQL database. Is it possible to restrict that account from accessing things beyond vendor tables/functions?

For example. I don't want this user to be able to call anything like @@SomeFunction or some_function() if it is something that is native to SQL Server. I also don't want it to be able to call into things like syslogins / sysobjects /syscolumns, etc.

So something like this: SELECT *,db_user() FROM sysobjects - Not allowed

Something like this: SELECT *,vendorfunction(column) FROM VendorTable1 INNER JOIN VendorTable2 ... Allowed

Is there a way to blanket deny native SQL functions/tables/Stored Procedure to a user?


texts:
Which version of VB does MS Access use in the code editor?  Is it VBA, VB.Net, other? I sometimes get confused when searching for help/solutions. For instance, in Access, you can't do:

Dim s as String = "hello"

But you can do:

Dim s
s = "hello"


texts:
I have a requirement to access (read) a table/view in database B from database A. The application that needs to execute the query however uses an Application Role. From what I've read and tried, doing Module Signing is not an option.

What would be the next best approach?
Is that setting TRUSTWORTHY on for database A and use EXECUTE AS?


texts: Sql Server - Select while insert on another transaction gives unexpected results

texts:
I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it.

Let's say I have a table like this:

CREATE TABLE [dbo].[SomeTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SomeData] [varchar](200) NOT NULL,
[Moment] [datetime] NOT NULL,
[SomeInt] [bigint] NOT NULL
) ON [PRIMARY]

and I run this "insert 1000 rows within a transaction" query:

BEGIN TRAN t1

DECLARE @i INT = 0

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    INSERT INTO [SomeTable] ([SomeData] ,Moment, SomeInt)
    VALUES (CONVERT(VARCHAR(255), NEWID()), getdate(), @i)

    WAITFOR DELAY '00:00:00:010'
END

COMMIT TRAN t1

While this transaction is running, I'm executing a simple select:

SELECT Id, Moment, SomeData, SomeInt FROM [SomeTable]

It isn't always possible to reproduce it (apparently depends on timings) but sometimes select query will, after insert transaction finishes, return less than 1000 rows. In my ignorance I have believed that select will always return 1000 rows (given that isolation level is Read Committed), but obviously I have misunderstood how transactions and locking work.

However, if I put a primary key on Id column (which generates a clustered index), select query will, as long as I've tried, return all 1000 rows. Putting indexes some other way, with clustered index on composite key and a non-clustered index on some other columns, may again result in returning less number of rows than I've expected.

So, I have these questions:

Why select doesn't always return all rows committed by transaction?
If this is expected behavior what's the best way of actually making it work as I have expected? Basically, I want select to return the state of the table after (or before) the transaction, not some half-done data. Snapshot isolation is currently not an option. Putting TABLOCK seems to be doing the work, but is there a better solution? In real life I have tables that I wouldn't want to lock on this level if it is not absolutely necessary.
Why putting an index changes this behavior?

Thanks in advance.


texts: In mysql if table fit into buffer pool is there any benefit to have partition?

texts:
Lets say I have a table with 100M rows. All table have enough space on buffer pool. So is there any performance or others benefit to create partition on that table?

NB: Storage engine - InnoDB


texts: Replicate single table from SQL Express to SQL Enterprise 2014

texts:
We have a small branch app that stores some data in a SQL Express Database.

We want to have an almost real time copy of this table in our headquarters, were we have SQL Enterprise 2014. I say almost, because it doesn't matter if there is a 5 minute delay or something like that.

The main thing is that it's able to synchronise after a network disconnection between branch and headquarter.

Any solution that does not requiere licensing costs for the branch office?


texts: Join a specific table depending on the provided values in a table

texts:
I have Maps (as in a game map) which each can have one or more drops, where those can vary between being currencies, items among other stuff that is separated in multiple, different tables because the data can't be lumped together.

I designed a one-to-many relationship (A Map can have multiple drops) where I would be holding the ID of the item in one of these tables.

Here's a sample schema I designed for this question:

CREATE TABLE Currency (
  id SMALLINT NOT NULL,
  name TEXT NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE Item (
  id SMALLINT NOT NULL,
  name TEXT NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE Map (
  id SMALLINT NOT NULL,
  name TEXT NOT NULL,

  PRIMARY KEY (id)
);

And then the following table which would be the mapping of a map drops to the respective item.

CREATE TABLE MapDrop (
  map_id SMALLINT NOT NULL,
  table_name TEXT NOT NULL,
  object_id SMALLINT NOT NULL,

  FOREIGN KEY (map_id) REFERENCES Map(id)
);

I made the following DB-Fiddle regarding the question: https://www.db-fiddle.com/f/pjqdFEQwiaCXh93LNVwCf4/0 with short and sample data.

For example, on the Fiddle Map 1 would select Currency ID #1 and #2. And Map 2 would get data from both tables.

How can I select to the corresponding table (i.e. in this case Currency/Item) depending on the drop that's set for that map? Is there a better approach to tackle this?


texts:
We are looking at moving our data from IBM Domino to another backend.  One of the things Domino does really well is replicate (or synchronize as they call it) between a Server and Clients.  That means that Users can take their mailbox, or any other application DB, with them and work offline and when they have access to the server the "master" on the Server synchs with the "slave".

Does MongoDB support two 2-way synchronization? Assuming that it does, does it also support selective replication, where the data that is pushed out to the Client can be controlled at a field level.

Thx in advance for any insight.


texts:
Table has duplicate data in it.  I am in need of identifying the data that is duplicated in a structure like such - identifying each storeID that is duplicated by Hambasa

ABC BLUE 2
ABC Green 2
ABC Orange 2
BET Blue 2
BET Green 2
BET Orange 2

This is sample DDL and the query using Row_Number() I attempted.  What would be the proper way to get the result I am after?  I am actually wanting to delete the duplicates, so that each storeID only has 1 entry for each Hambasa.

    Declare @TMI Table (HAMBASA varchar(10), storeid varchar(10))
Insert Into @TMI (storeid, hambasa) VALUES
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC', 'Orange'),
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC','Orange'),
('NYC', 'Blue'), ('NYC', 'Green'), ('NYC', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange')

;With pan As
(
    Select HAMBASA, storeid
    ,RN = Row_Number() Over (Partition By storeid Order By HAMBASA ASC)
    FROM @TMI
)


texts: Trigger to create a variable Table to be sent to service broker

texts:
I've setup a service broker for the first time and tested it... Looks good.
Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing it the inserted and deleted tables as XML parameters which will send the message (containing the 2 XMLs) to the queue.

The activation procedure then processes the queue, and inserts the changes done to the initial table into an audit table... Works Great.

The Problem occurs when the update on the Price table is a massive one.  In my case, I tested a Price change update on 105 000 Items.  The XML Inserted and Deleted Variables, contain 1.3 million lines each... Which causes major issues: TempDB grows infinitely and the processor goes up to 95% which is understandable... but never seems to complete the operation.

The code for the Trigger is Price_TAB_Audit_trig.
The trigger calls the SendMsgServiceBroker procedure to send the message to the Queue. See comments below for activation procedure.

I'm trying to figure out how I can break down the inserted and deleted variables (messages) into smaller XMLs.
My questions/Thoughts are:
Can I create table variables and pass that in the message instead of XML? If so, how to create variable tables in a trigger?
Should I create global unique temp tables and reference them in the message?

Any other suggestion, is welcome.
Thank you in advance
JG


texts: Real time preview of SQL queries generated by applications

texts:
I need to know what SQL queries are generated by some application.

Is it possible to see it in some kind of sniffer?
I tried to look in Management Studio, but it shows only most expensive queries.


texts: Moving data from one database to another database with auto incriment keys

texts:
I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id).

The questions is, I have 2 databases(db_a and db_b) that have the same schema (tables 1,2,3 with same relations). Now how can I insert data from db_b into db_a in a way that I can keep track of the correct relations between table3 and the table1 and table2.

EDIT

This is not a one time thing, it will reoccur aperiodically.

New data is inserted into db_a on daily basis, however data inserts on db_b can happen with different regularity. The issue is how to keep track of the auto generated keys for table1 and table2 when inserting table3 data into db_a


texts: Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

texts: So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must be mapped into a already full index page, it will split the page into half and position the record accordingly. So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full. Will this reflect when we run sp_configure the next time ?


texts:
I have table, table B:

id   p.id    s.id
1      5       1
2      5       1
3      5       2
4      5       2
5      5       3

The result looks like this:

s.id   count
1       2
2       2
3       1

I have tried:

Select count(s.id) from B group by s.id

But its not giving me the required result. I am using MySQL.


texts:
I'm interested in the overall CPU utilization for a database in a time span.  I see how to get that, but I'm concerned about the query store retention settings.

I don't need detailed info about each query execution, but because of the FK from sys.query_store_runtime_stats to sys.query_store_plan, it appears that the overall resource consumption isn't aggregated in total, but only per-plan.  My concern is that if plans are dropped because of a Query Store Capture Mode of Auto and a Size-Based Cleanup Mode of Auto, then I think that means that the execution times for dropped queries won't be included in the totals for an interval.

The docs say "Infrequent queries and queries with insignificant compile and execution duration are ignored", and what worries me there is the word "and".  Will it drop a 1-time query that uses tons of CPU?  Since "Thresholds for execution count, compile and runtime duration are internally determined.", I don't know.


texts:
I created a deadlock alert including notification to an operator like this:

EXEC msdb.dbo.sp_add_alert @name=N'1205 Deadlock',
        @message_id=1205,
        @severity=0,
        @enabled=1,
        @delay_between_responses=30,
        @include_event_description_in=1,
        @database_name=N'myDB',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000';

This alert is registered for notification.

It's history says it never appeared although it is in place for months and there are multiple deadlocks per day. I created a deadlock manually. It shows up in the Log File.

What is missing for this alert to raise a notification?
Other notifications like e.g. 18456 Invalid Login work fine.


texts:
I have Teradata v16.10.0.
I want to create user with admin options: with possibility get to grant select any tables/views.

How to create such user right?


texts: What is the best database design for multi-language data?

texts:
I am designing a multi-language database for a piece of software with the following features:

data used in every country differs from other countries
data used in every country will be in 2 languages: English, and the native language
there will be a central place to control updating and deleting all data in all countries

I have 2 choices for these multi-language databases:

Tables of each entity for each country will have a column for English and native language

I think that way makes it easy and fast to update every country's data, and I can separate its own tables only to be used with software there.
But that means if I have 15 countries, I have to repeat tables 15 times.

A table for all languages as a reference, linked to a table for each entity that contains data for all countries in all languages

It will make my database simpler, with a smaller number of tables, and simpler for retrieving data.
But it will be dangerous to have all data for one entity for all countries in one table. So any attack or mistake can destroy my work for all these countries.
Can I arrange data in the same table to make every row of the same languages together?
Can I isolate data required for every country from central tables to be used with software ONLY there?


texts: How to transposing rows into columns in MySQL?

texts:
I have three table (student's_info,subject and marks):

this is table student's_info:

----------------
|s_id | s_name |
----------------
| 1 | Ali      |
| 2 | Jon      |
---------------

This is table subjects:

 ----------------
|sub_id | subject|
----------------
| 1   | Word     |
| 2   | Windows  |
| 3   | Excel    |
-----------------

and this is table marks:

 -----------------------
|id | s_id |sub_id|n_marks|
------------------------
| 1 |  1   |  1   |  50 |
| 2 |  1   |  2   |  44 |
| 3 |  1   |  3   |  90 |
| 4 |  2   |  1   |  32 |
| 5 |  2   |  2   |  56 |
| 6 |  2   |  3   |  66 |
------------------------

i used this code to transpose rows as column:

SELECT
students_info.*,marks.*,
CASE WHEN subject = 'Word' THEN marks.n_mark ELSE 0 END AS `Word`,
CASE WHEN subject = 'Windows' THEN marks.n_mark ELSE 0 END AS `Windows`,
CASE WHEN subject = 'Excel' THEN marks.n_mark ELSE 0 END AS `Excel`

FROM students_info
INNER JOIN marks ON students_info.s_id = marks.s_id
INNER JOIN subjects ON marks.sub_id = subjects.sub_id
GROUP BY students_info.s_name;

This is my question: I wan to the name subjects in the table subject appear at the header of my table, i don't write name subject by wizard with this{CASE WHEN subject = 'Word'}...

I do not want to write subject tables in my codes for myself , because I have the name subjects  in the subject tables ,that is why i need a code to select name subjects.

see this image to understand my question:

Please review my code and give me a help, to do work this code.

Thank you in advance.


texts: Way to ignore pages in memory for 1 query

texts:
Is there some way to ignore both cached query plans and any data pages in memory for a single query or batch?  Something like a table hint or other option which can be turned on just for the current connection?

I want to force this query to hit the disk, because I'm trying to tune it and the execution times are all over the place.  Trying to reduce variables in play.


texts: Linked Server using Impersonation fails SSMS GUI Connection test but sp_testlinkedserver succeeds

texts:
I have a linked server (Server B) set up on Server A with permissions set as follows:

This means that there are five Windows Logins (of which my windows account is one) who can access the linked server on Server A and the permissions they will have when using the linked server are the permissions they have set on Server B.

If I log into SSMS as myself on my local machine (which isn't the server), connect the object explorer to Server A, right click on the Server B linked server and select "Test connection" I get the following error:

However, when I run the following code on Server A, the command completes successfully:

DECLARE @name SYSNAME = 'ServerB'
EXEC sp_testlinkedserver @servername  = @name

I can also verify that the linked server works by querying it as below:

SELECT TOP 1 1 FROM [ServerB].[MyDb].[dbo].[MyTable]

which returns 1 as expected.

Why does the connection test in the GUI fail when clearly everything is working as it should be?


texts: PostgreSQL - tuning multiple clusters on server

texts:
I usually use http://pgtune.leopard.in.ua/ for tuning postgresql parameters depending on hardware resources.

But now I want to tune two clusters on same server.

How should I tune PostgreSQL configuration in this case?

For the sake of simplicity let say both clusters would be under similar load.

Now with this setup:

DB Version: 10
OS Type: Linux
DB Type: Online transaction processing systems
RAM: 2 GB
Number of CPUs: 2
Data Storage: SSD

pgtune.leopard recommends this configuration:

max_connections = 300
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1747kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

So do I need to "slice" parameters in half or generally just use suggested configuration for each cluster? Or maybe it is recommended to use some different strategy here?


texts: Multiple Basic Availability Groups and Performance - SQL Server

texts:
I'm migrating some of my company's SQL Server boxes to a new SQL 2016 setup. We're currently using database mirroring on SQL 2012. One of the instances I'm migrating currently has about 60 databases and despite the occasional hiccup, mirroring is working fine for us. And for various reasons this instance is running SQL Standard edition. As mirroring is being deprecated, I'm keen to try Basic Availability Groups.

However, I'm running into real performance issues putting together a lab with only 20 databases. During the initial setup, simply creating a new availability group will exhaust the 472 thread limit for Availability Groups (Max Threads - 40). On the primary server I'll see something like this:

time   active_workers idle_workers worker_limit worker_start_success
--------------------- ------------ ------------ --------------------
12:01  473            0            472          true
12:02  473            0            472          false
... multiple rows elided
12:05  472            451          472          false
12:06  22             16           472          true

Simply trying to view the dashboard for one of the availability groups will peg the CPU for several minutes. This isn't a super powerful VM, but VMWare tells me it has allocated 3014 MHz to it.

This is also without instrumenting any database operations or I/O on the database side.

Are there performance considerations I'm missing? At this point, I feel like Basic Availability Groups won't work for my situation, but I want to be sure I'm not overlooking something. I've leaned heavily on these articles/blog posts:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
AlwaysOn Availability Groups – What Not to do when Adding Databases

Finally, this is the PowerShell script I've been using to automate the creation of availability groups. I may be doing something incorrect here:

Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab1"
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab1" -BackupAction 'Log'

Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab2" -NoRecovery
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab2" -RestoreAction 'Log' -NORECOVERY

$PrimaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab1" -EndpointUrl "TCP://vm-sqllab1.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab2" -EndpointUrl "TCP://vm-sqllab2.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13

$AgName = "vm-ag-" + $DatabaseName
New-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\vm-sqllab1\Default\" -Name $AgName -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica)  -BasicAvailabilityGroup -Database @($DatabaseName)
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\vm-sqllab2\Default -Name $AgName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\vm-sqllab2\Default\AvailabilityGroups\$AgName -Database $DatabaseName


texts: PostgreSQL 10.3 pg_dumpall fails: server closed the connection unexpectedly

texts:
System: Windows 10 Pro 64bit, Version 10.0.17134 Build 17134. with Postgres 10.3 db server.

I backup my postgres db using the following statements:

"C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U postgres -h localhost -p 5432 --schema-only --clean --file=dumpall_schema_clean_20181101.sql

"C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U postgres -h localhost -p 5432 --globals-only --clean --file=dumpall_globals_clean_20181101.sql

"C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U postgres -h localhost -p 5432 --data-only --clean --file=dumpall_data_clean_20181101.sql

When running the data-only statement I get an error message

  pg_dump: Dumping the contents of table
  "allwaysDifferentTableName" failed:

  PQgetCopyData() failed. pg_dump: Error message from server: server
  closed the connection unexpectedly
          This probably means the server terminated abnormally
          before or while processing the request.

  pg_dump: The command was: COPY schema.allwaysDifferentTableName
  (id, ...)
  TO stdout; pg_dumpall: pg_dump failed on database "DATABASE", exiting

This seems to happen at random times and tables, sometimes the error comes after a few GB have been backed up, sometimes after 6 TB. Also, the table that appears in the error message is different all the time.

The log file for the time the error happend:

  2018-11-07 00:52:59 HKT [25088]: [2-1]
  user=postgres,db=DATABASE,app=pg_dump,client=127.0.0.1 STATEMENT:
  COPY schema.allwaysDifferentTableName (id, ...) TO stdout;

  2018-11-07 00:52:59 HKT [25088]: [3-1]
  user=postgres,db=DATABASE,app=pg_dump,client=127.0.0.1 FATAL:
  connection to client lost

  2018-11-07 00:52:59 HKT [25088]: [4-1]
  user=postgres,db=DATABASE,app=pg_dump,client=127.0.0.1 STATEMENT:
  COPY schema.allwaysDifferentTableName (id, ...) TO stdout;

  2018-11-07 00:53:00 HKT [23288]: [1-1]
  user=postgres,db=postgres,app=pg_dumpall,client=127.0.0.1 LOG:  could
  not receive data from client: An existing connection was forcibly
  closed by the remote host.

The Postgres server runs on the same physical machine and I am not sure why it has a connection problem. I read, that a virus scanner may cause this problem, so I even turned that off. I also ran VACCUM ANALYZE prior to the backup.


texts: Restore MySQL tables with data folder only?

texts:
I have many MySQL databases with many tables and I’m trying to figure out if I can restore all of it only using the content of my MySQL data folder.

Or should I just not bother and use the following instead?

mysqldump --all-databases > db_backup.sql


texts:
I have a oracle 11g (11.3) database running with two standby databases (at different locations) in Maximum performance mode. The platform is Linux.

Log transport parameters are:

log_archive_dest_5 string SERVICE= * * * * **ASYNC NOAFFIRM** valid_for= ... **

Correct me if i'm wrong, so ASYNC noaffirm means the log transfer method is ARCH right? Meaning the archives generated in primary gets copied to standby and then applied there. Right?

The database is primary has been generating too much archives beccause of which my DR lags behind when archives are more. Oracle MOS asks me to create standby redo to resolve the same. My question is that how will standby redo help in reducing the lag since the SRL is used so as not to lose data in case of disaster right?

Also, SRL should be used only when the parameter is LGWR right? Since i have mentioned it as ASYNC NOAFFIRM and there is no standby, the archives are only sent to DR and not redo.

IF I do not have Standby redo, then how does redo gets shipped? Does it gets shipped as archive logs from primary to standby's archive directly and gets applied?

Help me clear out my doubts.
Thanks!


texts: We want to add a Kafka stream to publish changes to a table. The stream will be populated through the bin log and Atunity CDC. How do I seed the change data with the initial state of the table?


texts: What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive?

texts:
Possible Duplicate:
  Files - in the database or not?

I've been reading "Programming Entity Framework Code First" (http://www.amazon.com/Programming-Entity-Framework-Code-First/dp/1449312942).

There are a number of examples given where information about a particular person is stored in a SQL table, including the person's photograph (a digital image stored in a VARBINARY field).

In all of the systems that I have worked on, any kind of file associated with a record in SQL is stored on an ordinary network drive; not in SQL.  I would imagine that there must be some reasons for this, so here's my question:

What are the advantages and disadvantages of storing binary data in a SQL table vs storing the same binary data on a network drive given that the binary data is associated with a record in SQL?


texts: MySQL - How to see the queries executing by slave io thread

texts:
We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using mysqlbinlog utility. But we would like to see on the slave server what queries it is executing in particular time. We have enabled general log to see the queries but we have commit statements only.

How to see the queries executing by replication thread?

We are using MYSQL 5.6.10 for both master and slave.


texts:
I am running a REINDEX but it takes long time and still working.  The application using the DB is not responding right now. Is there a safe way to stop it and do it another time?
PostgreSQL version is 8.4.


texts: PostgreSQL Group by to create a new group whenever column value changes


texts:
I need to copy an entire SQL Server instance, with all databases from one server to another server in my test lab.

I am aware that this can be done the "cold way", by stopping/pausing the databases and copying the database files to new location and attaching them.

Is it however possible to do it the "hot way," using the full and diff backup files?


texts: Invoke SQLCMD doesn't work when used with xp_cmdshell

texts:
I am trying to get  disk space report from bunch of servers and insert them into sql table..

below is sample of what i am trying to do

set @ps = 'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume '+@servernames+'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label

$insertquery="
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           (''$pscomputername'' ,
           ''$name'' ,
           ''$capacity'',
           ''$freespace'',
           ''$Label''
           )
GO
"

Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname

}

"';

Now i try to pass the variable to xp_cmdshell like below

execute xp_cmdshell @ps;

When invoked in SSMS,above returns null,but works in powershell..

Any ideas why ?

Below are a few things i tried

1.Same account(admin) is used in both shell and ssms
2.tried multiple things like import modules
3.XP_CMDshell works,but this returns null only for this query
4.I have tried to add -nowait,but that doesn't help as well

I have been trying to get this done from more than a day,but this doesn't work..
I have to use xp_cmdshell because writing a c# app is not allowed.Bat file doesn't help because server names are passed as a comma seperated list.

Please let me know if you need any further info


texts:
We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do a statistics update with FULLSCAN. When it slows down, a sp_recompile "fixes" the issue, until the nightly statistics job runs again.

By comparing the slow and fast execution plans, I have narrowed it down to a specific table/index. When it runs slow it is estimating ~300 rows will be returned from a specific index, when it runs fast it estimates 1 row. When it runs slow it uses a Table Spool after doing a seek on the index, when it runs fast it doesn't do the Table Spool.

Using DBSS SHOW_STATISTICS, I graphed out the index histogram in excel. I would normally expect the graph to be more "rolling hills", but instead, it looks like a mountain, the highest point being 2x-3x higher than most other values on the graph.

If I update statistics on it, without FULLSCAN, it looks more normal. If I then run it with FULLSCAN again it looks like I described above.

This feels like a parameter sniffing issue, and specifically related to the (seemingly) weird index distribution above.

The proc takes in a table valued parameter, can parameter sniffing occur on a table valued parameter?

EDIT: The proc also takes 12 other parameters, some of which are optional, two of which are a start and end date.

Is the histogram odd, or am I barking up the wrong tree?

I am certainly comfortable trying to adjust the query and/or try to adjust my indexing. If that is the fix that is great, at that point my question is more about the skewed histogram.

I should mention that this is a PK IDENTITY clustered index. We have two systems that talk to each other, one a legacy system, one a new home-grown system. Both systems store similar data. To keep them in sync the PK on this table in the new system is incremented when things are added to the old system, even if the data doesn't come over (a RESEED is done). So there could be some gaps in the numbering in this column. Records are rarely, if ever, deleted.

Any thoughts would be greatly appreciated. I am more than happy to gather/include more info.


texts:
Previously I've asked the similar question MySql Switching Masters During Failover with log-slave-updates.

Let's say this is our current replication

In addition, I have few application servers. Every app server connects to Master 1 directly in order to INSERT and UPDATE and to Slave[1-3] in order to SELECT.

My question is how exactly can I promote Master 2 to be a real and the only master. Importance notice that Master 2 and all slaves below it are mysql 5.5 and Master 1 is mysql 5.2

When I changed IP of Master 1 to IP of Master 2 in application settings and made deployment, there was a moment when I had UPDATE and INSERTS both on Master 1 and Master 2, which caused "Duplicate entry ..." on Master 2. After that I rebuild the tree and run STOP SLAVE on Master 2 few seconds before the deployment. Of course, I lost some data, that was inserted to Master 1 and not replicated to Master 2.

So, what is the right way to do so without losting data.

In addition, I still don't understand what to run on Master 2 after STOP SLAVE in order to reset "slave settings", it looks like I don't really have to do this, just STOP SLAVE and everything should be working, but just to keep the order I want to reset "slave settings".


texts: Show values from list that are NOT returned by query

texts:
I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query.

Pretending that the list I was given is: 'something_a', 'something_b', 'something_c', 'something_d'...

My intuition is to do something like this:

SELECT item FROM ('something_a', 'something_b', 'something_c', 'something_d')
WHERE item NOT IN (
    SELECT appropriate_column FROM some_table
    WHERE appropriate_column IN ('something_a', 'something_b', 'something_c', 'something_d')
);

I would expect to get back the following result if say two of the items are missing from the table:

item
---
something_a
something_c

What is a valid SQL approach to accomplishing what I'm after?


texts: ORDER BY using custom priorities for text columns

texts:
This is a sample table:

name | cat
----------
hugo | sal
bob  | mgr
mike | dev
jeff | mgr
john | dev

Both name and cat being Varchar.

cat reflects a category for these names, but I like to assign a priority to them, followed by a query to list them all, ordered by this priority.

What ways are possible? I could extract cat into a second table, build a foreign key etc. But given my app is very simple and the structure might be non-changeable: What possibilities do I have to list the mgr names first, followed by the dev names, followed by the sal names?


texts: How to ensure a field is populated before use in a trigger

texts:
When I assign a new bed number in a hospital, the number must be prefixed by the ward number the bed is in. How do I know my user is going to populate Ward_Id before Bed_Id? What does one do in a case like this?

There is only one table:

CREATE TABLE [dbo].[Bed](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Bed_Id] [nvarchar](10) NOT NULL,
    [WardId] [int] NOT NULL,
    [Private] [bit] NOT NULL,
    [SpecialInfo] [nvarchar](max) NULL,
    [AdministratorId] [int] NOT NULL,
 CONSTRAINT [PK_Bed] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When a bed gets added, Bed_Id must be prefixed by WardId.


texts: What does it mean when I see a lock in sys.dm_tran_locks and there's no matching row in sys.dm_exec_requests?

texts:
I'm running this query

select sessions.session_id from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id

and it consistently outputs around four thousand rows. Yet when I run

select session_id from sys.dm_exec_requests

it outputs no more than several rows. And if I try this

select sessions.session_id, requests.session_id
    from sys.dm_tran_locks locks inner join sys.dm_exec_sessions sessions
       on locks.request_session_id=sessions.session_id
       left outer join sys.dm_exec_requests requests
         on sessions.session_id=requests.session_id
order by requests.session_id

I see that none of sessions.session_id are null but notably more than half of requests.session_id are null.

What does that mean? Where do those rows in sys.dm_tran_locks belong than I cannot find a matching row in sys.dm_exec_requests?


texts: Is it OK for the database to be completely overwritten for many times?

texts:
A simple and maybe too amateur question, but: is it good for a table on the database to be consistently (for example, every day) completely overwritten?

I get regularly (every day) the data for the whole year from the beginning of the year until now. I could just append the new days to the table, but I fear that there are some new data (adjustions or so) for the past days in the file. So I want to overwrite my table every time I get a new file. But is it ok for the table? I mean can it somehow corrupt it?


texts: Can I use LetsEncrypt to secure SQL Server?

texts:
I am running SQL Server 2014, and use LetsEncrypt to encrypt some websites on the server.  I want to remain PCI compliant, which requires an encrypted connection to the database, if accessing from outside the network (I want to use SSMS).  Would it be possible to leverage the WACS tool to secure a certificate for SQL Server external connections, with automatic updating of the certificate?

To be honest, I'm a little fuzzy on the details of assigning a certificate to SQL Server for external connections in the first place. (I get the gist, I think, but trying to put it together with WACS is another story...)

Yeah, I get it's bad practice to leave external connections open, but it sure does make it convenient to not have to remote into a server to access SQL Server Management Studio, to modify necessary things in the database, on a regular basis.  Not only that, I'd rather not work directly off the server.


texts: No Maintenance Plans Folder in SQL Studio 2008 R2

texts:
A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server.

Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy either, as it would be if there were legacy-mode databases on the server. I am certain this is SQL Server 2008 R2 and not Express. I have checked the version and verified the splash screen when studio starts are not Express. I am logging in as the SQL account sa, and the account does have the sysadmin server role.

Lastly, right clicking on a database in SSMS and navigating to "Tasks" does not show any Maintenance Plan option.

Clearly, I'm missing something, and Google is coming up empty considering the scenario I've outlined. Any ideas? Thanks.

EDIT: select @@version returns "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)   Apr 22 2011 11:57:00   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.2  (Build 3790: Service Pack 2)"


texts: Shared logarch across multipe db2 HADR instances

texts:
Background

We are looking into building a multiple standby hadr cluster. The Auxiliary standby is in a geographically separate location.

from Developer works

  In a multiple standby system, the archived log files can be scattered among
  all databases' (primary and standbys) archive devices. A shared archive
  is preferred because all files are stored in a single location.

and Developer works

  Share archive for databases at the same site is recommended. For remote
  sites, you will need to make a decision based on the network speed,
  reliability, and ease of management

My Question

What method/solution can be used to create a shared archive.

We are running on X86 SLES 11.4 with HDS san.
Primary and Standby is in the same datacentre but Aux-standby is 30km away.
Would a simple NFS share be used and replication to the Aux.

Thank you


texts: Design DB for users with different information fields?

texts:
Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for example for CS and economic and medicine we may have:

CS:

ID:123
Name:Ali
Lname:Alipour
projects:p1, p3, p6
programming Skills:C[5 star],Java[5 star],JS[3 star],etc.
Research Interest: Network

Medicine faculty:

ID:456
Name:Jafar
Lname:Jafarson
Hospital experience:hospital1 (2 year), Hopital2(1 month),...
Research Interest: Human body

Economic faculty:

ID:789
Name:Sadegh
Lname:Alipour
Company: Company1(company_name, add, tel,etc), Company2(company_name, add, tel,etc)
Research Interest: online currencies

We also may have students in the system:

ID:st_123
Name:mahdi
Lname:Mahdiyar
Major:CS
reg_date:2013.09.09

My first guess was to make a user_table and place all the common fields in it and then make field specific table for each major, CS_faculty_table, Medicine_faculty_table, Economic_faculty_table and student_table. Then thought about inheritance and polymorphism is OO programming language and if it is available in DB area. Then I saw accepted answer of this question that says you can have different fields inside a JSON file and store it in your DB as a BLOB field!

I'm new to DB filed and don't have enough experience in designing DBs so I wanted to know what's the best approach in this kind of situations?

I want a general answer. I don't know if the DB differs or not, and I don't care to change my DB to another open-source DB (sql or no-sql). I had PostgreSQL in mind.


texts: Multi-Instance Windows Failover Cluster - Min / Max Memory Allocation Dilemma

texts:
We run numerous 2 node SQL Server 2008 R2 Failover Clusters in Windows Server 2008 R2 on a VMWare eSXi virtual platform.  Each cluster hosts 3-4 SQL instances with varying degrees of memory requirement.

At present we use Max memory settings in SQL to allow for all 3 or 4 instances (with the greater of 10% or 4GB overhead for the OS) to run on a single node at any one time without impacting performance.  I believe this is the widely accepted method for memory allocation on cluster if you're not using Aaron Bertrand's handy script for dynamic memory allocation, but does inevitably lead to wasted resource.

I recently got around to reading an Idera Whitepaper "Top 5 SQL Server Cluster Setup Mistakes" written by Kendra Little, who I massively respect, which implies that this approach may not actually be optimal.  The whitepaper references a Microsoft article: https://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx.  Specifically to this scenario:

  Use min server memory to control memory usage. Establish minimum
  settings for each instance, so that the sum of these minimums is 1-2
  GB less than the total physical memory on your machine. Again, you may
  establish these minimums proportionately to the expected load of that
  instance. This approach has the advantage that if not all instances
  are running at the same time, the ones that are running can use the
  remaining free memory. This approach is also useful when there is
  another memory-intensive process on the computer, since it would
  insure that SQL Server would at least get a reasonable amount of
  memory. The drawback is that when a new instance (or any other
  process) starts, it may take some time for the running instances to
  release memory, especially if they must write modified pages back to
  their databases to do so. You may also need to increase the size of
  your paging file significantly.

So, my question really, does anyone have any experience of this?  I feel it might be a better idea to optimize memory usage in my environment as a minimum per node memory setting that would safely allow for all instance plus OS would then allow SQL more access to the otherwise unallocated RAM on the VM.

An example of what I mean:

Current Config:
2 x 32GB RAM VMs

VM1:

SQL Instance A - 0 Min Mem / 6GB Max Mem

SQL Instance B - 0 Min Mem / 8GB Max Mem

VM2:

SQL Instance A - 0 Min Mem / 6GB Max Mem

SQL Instance B - 0 Min Mem / 8GB Max Mem

Total Cluster Usage: 28GB Max SQL Memory

Suggested Config:

VM1:

SQL Instance A - 6GB Min Mem / Default Max Mem

SQL Instance B - 8GB Min Mem / Default Max Mem

VM2:

SQL Instance A - 6GB Min Mem / Default Max Mem

SQL Instance B - 8GB Min Mem / Default Max Mem

Total Cluster Usage: 28GB Min SQL Memory

Any thoughts and comments would be gratefully received.


texts: Automating timestamp fields in PostgreSQL

texts:
I'm trying to define the following table in PostgreSQL:

posts
- uuid
- title
- created_at
- updated_at
- deleted_at

The idea is that i never delete anything, so i change the deleted_at instead of removing the row and use where deleted_at is null for normal behavior.

Is there a way of automating those timestamps?


texts: Password validation in AWS RDS Postgres database

texts:
Is there way we can check/set password validation for PostgreSQL RDS database?
like in MySQL we have password validation plugin.
How long password should be/password expiration date etc.
I have checked below link but this is not which i am looking.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.RestrictPasswordMgmt
Regards,
Adam Mulla


texts: Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

texts:
Not sure if this is a generic question, but unless not, this is MySQL 5.6.

We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be seen as the parent. My desired end result is that I can execute one delete on this table, and have all related information in all other 300 tables deleted as well.

However, the declared foreign keys are a mix of RESTRICT and CASCADE for ON DELETE and I'm trying to understand the rule set when these are applied in recursion.

I've been trying to find information on this, but no luck. Testing appears to indicate that RESTRICT blocks a CASCADE, but not necessarily. Consider this example:

  Tables: A, B, C
  B -> A (ON DELETE CASCADE)
  C -> A (ON DELETE CASCADE)
  C -> B (ON DELETE RESTRICT)

In this example, rows from B and C are deleted when A is deleted. However, since C -> B has a RESTRICT one could think that matching rows would block a CASCADE. But this is not the case. However, if I add an additional table D like this:

  Tables: A, B, C, D
  B -> A (ON DELETE CASCADE)
  C -> A (ON DELETE CASCADE)
  C -> B (ON DELETE RESTRICT)
  D -> C (ON DELETE RESTRICT)

A delete from A is blocked by the RESTRICT from D to C.

A simple approach would be to simply use CASCADE everywhere, but I'm curious about the rule set. Or if the behaviour is either implementation dependent or simply undefined.

Is it based on level of depth of the CASCADING? That is, the shortest path determines the effective ON DELETE rule to use? What if multiple paths have the same length, with different rules?


texts: I’m looking at some of the SQL servers in a (new to me) SQL estate. The servers are configured for AOAGs; there are two nodes, both are part of the WSFC. However, if I look at the SQL Server advanced properties the 'Clustered' attribute = 'No'. Would we expect them to be in a SQL cluster sat on a WSFC? In which case I would expect the 'Clustered' attribute = 'Yes'.


texts:
I have a question about HAG / DB synchronization in MS SQL server.
I have a HAG and in the secondary node I had to remove one DB on Saturday from the HAG. I added it back on Sunday and it's been out of sync since then. This secondary node it's read-only and more like a backup so it's not impacting anything.
I want to know what would be the best approach to put this DB back in sync. Right now the DB it's in the available databases but not synchronizing.
If I join this database to the HAG, it will automatically start seeding from the primary or is it better to restore the last full backup and t-logs and then join to the HAG?
The DB it's quite large. I tried restoring it in another VM and it took approximately two days and a half so I guess that I should stop the full backup for that DB in specific and just restore the last full backup and all t-logs until up to date right?
Thanks!
EDIT: I ended up restoring the full last backup with the t-logs in norecovery in the secondary node and then just joining the DB into the AG. Worked like a charm.
Thanks for the responses!


texts:
I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server.

One of the databases has some computationally intensive queries run on it from time to time. This causes the CPU utilization to jump up to 100% and the sevrer often does not respond to requests.

How to Prevent heavy queries on one database, affecting the performance of other databases on same server? How can I make sure that other databases on that PostGres Instance still respond to requests? Is there a way to prevent one database from consuming all the CPU cycles?


texts:
I've tried to show the SQL code of a stored procedure called add_pixel:

mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I've made sure that the procedure exists:

mysql> CALL add_pixel();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE pixels.add_pixel; expected 3, got 0

And SHOW CREATE PROCEDURE works:

mysql> SHOW CREATE PROCEDURE add_pixel;
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| add_pixel |          | NULL             | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

and that my MySQL version supports `SHOW PROCEDURE (SHOW PROCEDURE CODE was added in MySQL 5.0.17).

How do I view the source SQL code of the procedure?


texts:
I am facing a issue with 3 node galera cluster where the read doesn't return any records even when the write has been commited. Below are the steps which can happen from multiple servers.

1>Server A writes a record at 12:00:00 AM

2>Server B reads the same record after 10 seconds at 12:00:10 AM but doesn't get any record.

The bin log on all 3 server confirms the commit at 12:00:00 AM.

What could be the issue here ?


texts: Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database

texts:
We have one master table and multiple child tables (dependent tables)  associated with it.

While taking the backup of the master table , I would like to take the backup of all its child (dependent) tables backup also.

Please guide me how to take the backup of the  maste table and its dependent tables using pg_dump commad.


texts: How to combine multiple EXEC statements into a single SELECT?

texts:
I run this every month to extract database logs, and I have to change the dates as well.
DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');

DECLARE @end DATETIME
SET @end   = CONVERT(DATETIME, '2020-08-31 23:59');

DECLARE @searchString1 NVARCHAR(256) = 'BACKUP';
DECLARE @searchString2 NVARCHAR(256) = '';

EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 2, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 3, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 4, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 5, 1, @searchString1, @searchString2, @start, @end;

However, I get multiple results that I have to manually sift through and combine
How can I do something like this:
SELECT * FROM (EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
SELECT * FROM (EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
etc

Obviously, this does not work. I get

Incorrect syntax near the keyword 'EXEC'.

Is there a better way?


texts: what are the options for the psql \copy command?

texts:
I was looking at the psql documentation:

  \copy { table [ ( column_list ) ] | ( query ) } { from | to } {
  'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [
  [ with ] ( option [, ...] ) ]

but couldn't quite figure out what are the possible options in the last with clause. Looking at \? \copy in psql doesn't give much information either. Any pointers to what are these options?

The only one I know so far is with csv. I have a space separated file, which I don't know how to \copy into postgres via psql.


texts: Sending a mail using DB2's Proceude UTL_MAIL

texts:
I'm trying to send mail from DB2 using the procedure UTL_MAIL.
I'm using the DB2 Express Edition 10.1.
First I was getting the error of SMTP Server.
After using the below command,

update admin cfg using smtp_server LOCALHOST

I am using the following command to send the mail:

CALL UTL_MAIL.SEND('test@mycomp.com','myid@hotmail.com',NULL,NULL,'Test Email','Test body')

Now I am getting the following error:

SQL1336N  The remote host "" was not found.  SQLSTATE=08001

I searched a lot and found out that I must CATALOG the server details.

Can anyone please help me regarding this?


texts: get max date by comparing total amount with amount in another table

texts:
I have two tables tableA(S.no.,amount) and tableB(Id,paid_amt,trxn_dt)

   tableA                tableB
S.no.  amount     Id    paid_amt    trxn_dt
1       500       1       200      2015-01-01
2       500       2       300      2015-02-10
3       500       3       500      2015-04-10
4       500       4       1000     2015-10-10

Now i need a query to get the output as follows:

S.no.    amount          trxn_dt
 1        500           2015-02-10
 2        500           2015-04-10
 3        500           2015-10-10
 4        500           2015-10-10

I need to compare the sum of paid_amt from tableB to match each amount of tableA and get the max trxn_dt according to it. i.e (200+300)=500  so get max(trxn_dt) i.e 2015-02-10. Similarly for s.no.2 get get max(trxn_dt) as 2015-04-10 and for last one 1000 has been paid on 2015-10-10 so get max(trxn_dt) as 2015-10-10 for both s.no. 3 and 4. i.e i have to get the trxn_dt of the exact paid amount according to s.no. I am able to do this using resultset in java but it is time consuming for table containing lots of data.I would appreciate any kind of help. Thank you.


texts:
I am trying to convert the following SQL row level security function into a DAX filter within a Tabular model

CREATE FUNCTION [Security].[fn_securitypredicate](@BrandID AS INT, @ChannelId AS INT)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
     (SELECT 1 AS fn_securitypredicate
            WHERE (EXISTS ( SELECT 1 FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER AND BrandId = @BrandID)
              AND  EXISTS ( SELECT 1 FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) )

               OR (        EXISTS (SELECT 1  FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER  AND BrandId = @BrandID)
                   AND NOT EXISTS ( SELECT 1  FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER ) )-- this user is not restricted by Channel

                OR (NOT EXISTS ( SELECT 1  FROM   security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER)
                    AND EXISTS ( SELECT 1  FROM   security.RLSStaffChannel

WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) )

)
    GO

So far I have the following DAX filters, but this only handles the first condition in the SQL code. I don't know if it's even possible to replicate the rest in DAX.

='Brand'[BrandId]=LOOKUPVALUE('RLSStaffBrand'[BrandId], 'RLSStaffBrand'[StaffUsername], USERNAME(), 'RLSStaffBrand'[BrandId], 'Brand'[BrandId])

='Channel'[ChannelId]=LOOKUPVALUE('RLSStaffChannel'[ChannelId], 'RLSStaffChannel'[StaffUsername], USERNAME(), 'RLSStaffChannel'[ChannelId], 'Channel'[ChannelId])


texts:
Hi I am new to Oracle Database . Using Oracle 11g R2

I want to know the purpose of archive logs created at archive log destination, how they are helpful in database backup.

Suppose I am having backup of Production database till "15-sept-14" and I want to restore on Development Database data till "20-sept-14" , how to restore data using backup files and archive logs.


texts: how to force MySQL unsafe replication warnings to throw an error

texts:
I understand that MySQL WARNING statements, with respect to replication unsafe statements, are not 100% valid.

That is, if you know what you are doing, if you understand the schema, there are indeed many cases where the warning can safely be ignored.

However, that said, I find it particularly vexing that MySQL does not allow someone to force ALL 'unsafe' replication warnings to act as errors.

What value do such warnings have, warnings that essentially means "this query could cause your slave to have different data than the master!", when you only receive that warning AFTER the query has run.

If the query is one that does cause different data on the slave and master, well too bad, it's too late, and essentially the warning only means "Sorry buddy, you've screwed up, thought you'd like to know".

Some may suggest I turn on MIXED.  However, people often choose not to use MIXED for quite a variety of reasons.  And, what I am hoping for here is specific to STATEMENT based only.

So.

Does anyone know any way to FORCE all WARNING messages about unsafe SQL statements to instead throw ERROR?

And, this MUST be server side.  The problem is that even a skill DBA might accidentally write a query without fully considering things, or that a junior might make an accident.

Optimal, would be an ERROR thrown, but if someone prepended with "SET I_KNOW_THIS_IS_DANGEROUS_I_SAY_ITS_OK=1; ", then the query would run.

TL;DR what use is a warning about corruption, without even asking if it is OK to continue?


texts: Problem joining two tables on date field?

texts:
im using following query with PostgreSQL 9.1.13:

SELECT
    day::DATE AS date,
    COUNT(s.id) AS sale_count,
    COUNT(c.id) AS claim_count

FROM
    GENERATE_SERIES('2017-08-10', '2017-08-13', INTERVAL '1 day') day
        LEFT JOIN sale_order s ON s.date_confirm = day
        LEFT JOIN crm_claim c ON c.create_date = day

GROUP BY
    day.day

ORDER BY
    day.day

Expected Output:

date|sale_count|claim_count
---------------------------
2017-08-10|31|17
2017-08-11|32|23
2017-08-12|33|3
2017-08-13|21|0

Actual Output:

date|sale_count|claim_count
---------------------------
2017-08-10|527|527
2017-08-11|384|384
2017-08-12|99|99
2017-08-13|21|0

What am I doing wrong?


texts: Create tables under a different schema by default instead of [dbo]

texts:
I'm trying to get a user to create tables in a schema that is set as their default schema and they are set as the owner of the schema.
When I run this command, even though their default schema isn't [dbo], it still creates the table under the  [dbo] schema
EXECUTE AS USER = 'test'

CREATE TABLE TEST (T VARCHAR(10))

REVERT

The reason I'm looking to do this, is because the program that we're using, by default, uses the [dbo] schema to replicate data. It states in the documentation that tables will use the schema prefix of which user is running the stored procedure.
Is this possible? Any new tables created by this user, I want it to use their default schema instead of [dbo]


texts:
Sorry I have an similar question asked before.
But I'm a newbie with mysql and I don`t get it.. I don't know how it works.. hope someone can help me again. Thank you!

I have an SQL select, that gives one row back. The SQL update gives the error message 1093 back;

  mysqli error: [1093: Table 'ASSET_ASSOCIATION' is specified twice,
  both as a target for 'UPDATE' and as a separate source for data]

UPDATE ORG1.ASSET_ASSOCIATION
SET ASSOCIATED_ASSET_ID =
(
SELECT ASSET_ID FROM ORG1.HD_TICKET
WHERE ID = (<TICKET_IDS>)
)
WHERE ASSOCIATED_ASSET_ID =
(
SELECT ASSOCIATED_ASSET_ID FROM ORG1.ASSET_ASSOCIATION
WHERE ASSET_FIELD_ID = 11
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
OR ASSET_FIELD_ID = 111
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
OR ASSET_FIELD_ID = 1111
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
)


texts:
I have created and tested a report in Visual Studio and it returns data from a SQL server database.
Once uploaded to SSRS, the report returns old data.

What would I need to check as it appears the data source has cached?

Update: I have created a table in the SQL database, and can query it from VS and report builder, but not from a report in SSRS.


texts: Cascade primary key update to all referencing foreign keys in oracle

texts:
Is it possible to update all referencing foreign keys in Oracle when updating primary key?

A procedure like this that gets the table name and primary key and finds all foreign keys in all tables and update that with the new value

  PROCEDURE cascade_update_primary(
                          table_name IN VARCHAR2,
                          primary_column IN Number,
                          new_value IN Number);

Oracle does not support ON UPDATE CASCADE.


texts: Database design for different, but related, objects

texts:
I'm new to relational databases having enjoyed the flexibility of nosql to date.

I want to store data about 'estimates'. An estimate takes a set of parameters that depend on which product it is an estimate for. I think there are two ways forwards:

Tables for each product category

I was planning to have a table for estimates and one for each product category (a category could be "cards", and this might cover product such as "business cards" and "postcards":

estimates (id, name, user_id, category("Cards", "Books",...), linked_id (=one of the ids in the following tables)
cards(id, product, size, weight)
books(id, product, fold, coating)
...

I've read on Stack overflow some posts about conditional joins, but never got the impression that this was 'best practise', although I also did not see alternatives.

A table where not all fields are completed

Here I would have an estimate table that has every possible field for products, but only use those that are relevant for the product stored in that row,

estimates (id, name, user_id, category,size, weight, fold, coating, ...)
# example row
# id: 100, name: Recipe book, category: book, size: 77, weight: 20, fold: null, coating: null,...

Does the above make sense; what do I need to watch out for?


texts: Redundancy of information and algorithm to do lossless decompositions

texts:
Let be a relation R(NameFile, Size, Directory, DateCreation,HourCreation,Login,AutAccess,DateAccess,TypeAcces

F={NF,D→S;
   NF, D →DC;
   NF, D→HC;
   L,NF,D→A;
   L,NF,D,DA,HA→TA;
}

  Part of the instances are given in the following array:

Does the relation create information redundancy?
What would a decomposition of R without loss of information and without loss of dependency be?

I don't think so because it seems that the information given by the dependencies seem to be unique.

I know there is algorithm to decompose R without loss of information or without loss of dependency but I don't know them


texts:
I know this has been asked about multiple times in the past, sorry for the repeat I'm just trying to brainstorm a solution.

I'm looking for a way to query a table while excluding a small minority of the columns without having to query information_schema manually every time (since a lot of my tables consist of 50 or more columns only 2-3 of which are used for things that are irrelevant in the desired use case.)

I went about this like so:

query 1 returns the list of all the columns that exist on a table minus the ones you don't want.

        CREATE OR REPLACE FUNCTION public.inverse_columns(
        _table_name text DEFAULT NULL::text,
        _col_list text[] DEFAULT NULL::text[])
      RETURNS text[] AS
    $BODY$
    DECLARE
    col_arr text[];
    BEGIN
    EXECUTE  'SELECT array_agg(column_name::text) FROM information_schema.columns
    WHERE table_name = ' ||quote_literal(_table_name) ||' AND
    column_name NOT IN
    ('||chr(39)|| array_to_string(_col_list, quote_literal(chr(44)))||chr(39)||');'
    INTO col_arr;

    RETURN col_arr;
    END;
    $BODY$
      LANGUAGE plpgsql IMMUTABLE
      COST 100;
    ALTER FUNCTION public.inverse_columns(text, text[])
      OWNER TO postgres;

while query two uses the output array in another dynamic query in order to return the result set.

CREATE OR REPLACE FUNCTION public.except_select(
    _table_name text DEFAULT NULL::text,
    _col_list text[] DEFAULT NULL::text[])
  RETURNS SETOF record AS
$BODY$
DECLARE
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN QUERY
EXECUTE 'SELECT '|| quote_literal(array_to_string(ret_cols, chr(44)||chr(32))) ||
    'FROM ' || _table_name||';';
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION public.except_select(text, text[])
  OWNER TO postgres;

the error I am receiving is this:

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM except_select.....

and while I understand it is that I am misusing record, I can't think of a good way to return an undefined set of rows short of returning a refcursor and using that to populate a result set(which is what I am working on right now with slow progress unless I can get a better idea of how to do this.)

    EDIT:
few days of playing around with it and I gave up.


texts:
Can I create a new instance in a new server and import a dump file which was made with the command exp full=y etc?

If it is possible do I need to recreate tablespaces and users before the import? If yes can I find these two from the dump file alone?


texts: Is there some kind of table-level replication in PostgreSQL?

texts:
I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from database B need to reference some of their fields as foreign keys on table users.

So far I've tried:

Finding some kind of native table-level replication, so any change on users is automatically replicated to the clone table in database B. Didn't find anything.
Creating a TRIGGER on table users so after any change a function is called, but that doesn't solve anything since I can't insert values from database A into database B's tables.
Using FOREIGN TABLEs. Neither solves it, since no foreign keys may be applied to foreign tables.
Use a SCHEMA instead of DATABASE for A, but application enforces it to be a database.
I've even thought about a CRON each minute which would dump and import the table from A to B, but that would break any foreign key relationship with other tables.

I can't combine the two databases in one (although technically both have uniquely named tables), because when upgrading one of them it checks the database and should anything weird be in it, it would fail.

Do I have any other chances here? Any hint is very appreciated.


texts: Why VIEWS are showing in Data Transfer Service SQL

texts: Im trying to run DTS from one Db to another in Same server instance. But at the select objects to copy stage, its showing the VIEWs also. But VIEWS are only some customised queries. But why its been shows in DTS


texts: Postgres: Find previous and next row or existing value created by SERIAL colum for separate query

texts:
I got a Postgres 13 db and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps.
In each row binary data is stored too and I would like to process it row after row moving backward or forward.
My idea would be to do "SELECT id FROM tbl;" and then use this as a lookup table. Another idea would be to create a view with a new continuous sequence and to get the max value and then iterate between sequence start and max value.
EDIT:
Example:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    data BYTEA
);

INSERT INTO tbl (name, data) VALUES('a', '0xff');
INSERT INTO tbl (name, data) VALUES('b', '0xff');
INSERT INTO tbl (name, data) VALUES('v', '0xff');
INSERT INTO tbl (name, data) VALUES('w', '0xff');
INSERT INTO tbl (name, data) VALUES('t', '0xff');
INSERT INTO tbl (name, data) VALUES('u', '0xff');

DELETE FROM tbl WHERE id='3' or id='5';

As you can see the row id is having gaps.
I would like to do e.g. something like that (Written in Pseudocode):
std::vector<int> vec = SELECT id FROM tbl; -- -> Store values in C++ vector named vec

for(int i=0; i<vec.size(); i++) { -- Loop through vector resp. every row in the database table
    SELECT * FROM tbl WHERE id=vec[i] -- Query every row in tbl
    //Do something with the binary data -- Process its data
}

Is there a better way to achieve this?
If it plays a role, I am using libpq.
Thanks


texts: Inserting into table: either invalid datatype or duplicate column name error. (But the datatype is valid and there's no duplicate)

texts:
Spoiler: solution to this is here. the solution was really simple.
I'm trying to insert data from an external table.
Here's the code I'm trying to use:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)
SELECT *
    FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

But now i get this error:
Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Line 171 is the last line of this part
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)

Which is clearly not a duplicate column.
If I do:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT *
    FROM EXTERNAL ((
      ext.construct_id NUMBER (10),
      ext.n_term VARCHAR2 (50),
      ext.enz_name VARCHAR2 (50),
      ext.c_term VARCHAR2 (50),
      ext.cpp VARCHAR2 (50),
      ext.mutations VARCHAR2 (50),
      ext.mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

I get
Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Line 171 is ext.construct_id NUMBER (10),

Background:
The reason I'm listing the columns out instead of just saying insert into construct select  is because I have a virtual column in the construct table taht's defined like this: construct VARCHAR2 (200) GENERATED ALWAYS AS  (n_term || '-' || enz_name || '-' ||c_term || ' ' ||mutations) VIRTUAL,
And there's no corresponding column in the csv file for it.
The second column in the ERD called CONSTRUCT is defined like this:

Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.
(Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.)


texts: What is best way to storing geo coordinates related to planned route?

texts:
Here is my scenario:

Application "A" produces lat and long points that correspond to planned route. Depending on route distance it could have several thousands of points. I know of Geography and Geometry datatypes, even thought they came out with SQL 2008 I have not seen anyone use them yet and not sure what would be good scenario for using it. In addition to points that App "A" generates, I need to store points from App "B" that correspond to actual route. After it is all stored I need to find deviations from the planned route.


texts:
I am running Atlassian Jira software and using Postgresql as its database server.

We have a power interruption which turned off the host server and therefore the vm's didn't shut down correctly.

When rebooting the Jira machine, it wouldn't load Jira I forcefully started Jira to realize that it still wasn't connecting to the database.

When switching to user postgres and running psql I get the error

  Warning: No existing local cluster is suitable as a default target. Please see man_pg warpper(1) how to specify one.
  Error: You must install at least one postgresql-client- package.

There were two versions of postgres on this Ubuntu 18.04 machine 9.5 and 10.

9.5 is installed in /opt/PostgreSql/9.5

10, well I don't have a clue where that is. the only place I can find anything that says 10 is in /etc/postgres/main/10/

the only "data" I can find was in /opt/PostgreSql/9.5/data.

When restarting the PostgresQL service was masked, so I ran

systemctl unmask postgresql.service.

I then ran

sudo service postgresql start

It doesn't look like it started it fully, just some dummy services of it.

I then try to do sudo su postgres then navigate to /opt/PostgreSql/9.5/bin and run ./pg_ctl start but I get an error that says PGDATA isn't defined.

I don't know where to find that or what to do with it.

Is there a daemon or something I need to restart? Why is it saying that there is no version of PostgresQL installed? I am really lost here.


texts: SQL log directory has too much dump file

texts:
My SQL Server 2014 is automatically creating SQL dump file. It is generating automatically to take all the spaces and hangs over the database.

  Error: 8310, Severity: 16, State: 1.
  Cannot create (or open) named file mapping object 'Global\SQL_110_MEMOBJ_24_SQL14_0'. SQL Server performance counters are disabled.
  Error: 3409, Severity: 16, State: 1.
  Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
  Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)


texts: Append columns with values from has many reletionship

texts:
I have two tables orders and items. Every Order has many Items.

orders
- id
- created_at
- paid_at

items
- id
- title
- amount
- quantity
- order_id

Let say that there are 3 kinds of items. Chair, table and door.

I would like to create a query that will produce table with following columns:

- order_id
- created_at
- paid_at
- item_id # for chair
- item_title
- item_quantity
- item_amount
- item_id # for table
- item_title
- item_quantity
- item_amount
- item_id # for door
- item_title
- item_quantity
- item_amount

If order has chair item, data in columns for chair will be displayed otherwise it will be empty.

Why am I doing this?

I would like to export this data to excel sheet.

There is limited amount of different items so there is no problem with milions of columns.


texts: How can I convert from Double Precision to Bigint with PostgreSQL?

texts:
I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?

I have tried with to_bigint(myvalue) but that function didn't exist.


texts: Entity Framework SaveChanges fails due to a trigger

texts:
Trying to implement Entity Framework for an existing database (SQL Server 2012). I have a VB application running and trying to convert it to the EF based Web API.

When I insert data into the table InboundEquipment where InboundEquipmentID is an IDENTITY column. I also have an insert trigger for this table, which uses InboundEquipmentID to insert into another table Message214Status. No FK relationship between these tables.

Database objects:

CREATE TABLE [dbo].[InboundEquipment](
    [InboundEquipmentID] [bigint] IDENTITY(1,1) NOT NULL,
    ...

CREATE TABLE [dbo].[Message214Status](
    [InboundEquipmentID] [bigint] NOT NULL,
    ...

ALTER TRIGGER [dbo].[InboundEquipment] ON [dbo].[InboundEquipment] FOR INSERT AS
DECLARE
    @biInbndEquip_ID    BIGINT,
    @iCust_ID           INT,
    ...
SELECT  @biInbndEquip_ID= InboundEquipmentID,@iCust_ID= c.Cust_ID,  ...
FROM
    INSERTED I
    JOIN sometable c WITH(NOLOCK)ON ...
INSERT INTO dbo.Message214Status (InbndEquip_ID,    Cust_ID) VALUES
    (@biInbndEquip_ID,@iCust_ID )
...

My VB code:

db.InboundEquipment.Add(ibData);
try
{
    db.SaveChanges();
    var IBEquipID = ibData.InboundEquipmentID;
}
catch (Exception ex)
{
    return ResponseMessage(Request.CreateErrorResponse (HttpStatusCode.InternalServerError, "ERROR:" + ex.Message));
}

public partial class InboundEquipment
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long InboundEquipmentID { get; set; }
    ...

DBContext.db.SaveChanges() fails with this exception:

  InnerException {"Cannot insert the value NULL into column 'InboundEquipmentID', table 'dbo.Message214Status'; column does not allow nulls. INSERT fails.
      \r\nThe statement has been terminated."}    System.Exception {System.Data.SqlClient.SqlException}

Basically the insert trigger fails. I cannot change database objects since there is another existing application that depends on this database that cannot be changed.

When I disable the insert trigger,  the save completes successfully.

What could be the reason for the trigger failure?


texts: what kind of locks are applied by Azure Synapse when loading data using COPY INTO, if we are running extract from synapse dw (say from table A) and simultaneously loading new data into a some other (say table B). Will it cause any locks, will we face slowness or any locks while extracting data from table A


texts: Optimizing query using a WHILE statement

texts:
I'm trying to convert a view that extracts a yearly amount into something more WHILEish

The query is more or less:

SELECT *
FROM
(SELECT SUM(EmployeePaid)*2 AS Total2013 FROM TblRecords WHERE FYear = 2013) t1,
(SELECT SUM(EmployeePaid)*2 AS Total2014 FROM TblRecords WHERE FYear = 2014) t2,
(SELECT SUM(EmployeePaid)*2 AS Total2015 FROM TblRecords WHERE FYear = 2015) t3

So this returns:

=====================================
‖ Total2013 ‖ Total2014 ‖ Total2015 ‖
=====================================
‖ 42983.31  ‖ 76345.10  ‖  87233.5  ‖
=====================================

Which is nice and easy to use for generating a chart, for example. But as I mentioned earlier I started to look into this sort of query and I believe they can turn into something more compact as in a WHILE cycle. I'm aware of the old time If its not broken don't fix it so any input in the matter would be appretiated.

So far I'm stepping into something around these lines:

DECLARE @intYear INT
SET @intYear = 2013
WHILE (@intYear < Year(GETDATE()))
BEGIN
    SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = @intYear
    SET @intYear = @intYear + 1
END

Right now I'm stuck in trying to get the same output as the original query, since my WHILE its doing the trick but returning output for 3 different queries. I also cant manage to get CurrYear to "gain" a new value in each round, sort of like a CurrYear + @intYear kind of name.

Any hint is appreciated


texts: Duplicating some rows of a data flow in SSIS

texts:
I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing integer) against the row.

The new design has two tables, one for the "current" state of the data and an audit (or history) table which records changes using a trigger. Therefore only one row exists for the "current" data and there are many history rows.

In my SSIS package I am using the following components to copy the current data to one table but then send all the data to the audit table.

The Multicast is used to split the stream of data and the Conditional Split identifies the "current" row and sends that to the Order table (the table is not actually called Order, before anyone comments about using a reserved word for a table name).

I created this flow because I could not see a way to use the Conditional Split to send all the data to the Audit destination and only the current row to the other.

I'm assuming that creating all the duplicate data and then discarding it to a Trash Destination is not very efficient and since I have about 52m rows to migrate I'm worried the transformation is going to take days.

Is there a better (more efficient) way to achieve the data split?

Note on data: I have applied a row_number() to the data which allows me to identify the "current" row as number 1, all the rows including "current" need to go to the Audit table destination.

EDIT:
I have found an alternative to the Multicast and Conditional Split suggested by this blog post from SSIS Junkie: Multiple outputs from a synchronous script transform

It uses a Script Component to send data to one or more output. I'm trying that method to see if it's any faster but after seeing Kenneth's answer and suggestion about removing the Trash Destination I'm not sure it will be.


texts: Filter one column by value in second column postgres

texts:
I'm constructing a large table that is pulling values from across the database. Two of the fields I want to include in the new table are stored in a single column in another table. I want to split this field in the current table into two columns in my new table. Ideally, I would do this as part of a larger select statement but otherwise, I can use a CTE or temp table.

For example, the data is stored like this:

CREATE TABLE foo(id,title,type)
AS VALUES
    ( 1, 'Book Title 1',  'Book'    ),
    ( 2, 'Book Title 2',  'Book'    ),
    ( 1, 'Chapter 1',     'Chapter' ),
    ( 1, 'Chapter 2',     'Chapter' ),
    ( 2, 'Chapter 1',     'Chapter' );

And I'd want my new table to be like this:

|Book             | Chapter    |
--------------------------------
| Book Title 1    | Chapter 1  |
| Book Title 1    | Chapter 2  |
| Book Title 2    | Chapter 1  |

How do I write a SQL query that can perform this transformation?

Can this type of transformation be done as part of a larger SQL query or should I create the table and then join it to my other tables?

Or should I export the data and reshape it with R and the load it back into the DB?


texts: AlwaysON - Force users connection to use Readable Secondary Nodes

texts:
I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Processes like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.

The problem is: My client wants to force all non-admin users to connect always to the secondary-readable replica. BUT! make this automatically. The client does not want that the users have to add parameters to the connection in SQL Server Management Studio.

I'm testing logon trigger with no results. I can't find a way out to this.

Is this possible?


texts: query to select data between todays date as well as tomorrows date time upto specific time

texts:
I have database "raj" with table "pagination"

In table pagination have "id", "actualtime" and "created" field

id - auto_increment
name- varchar
created- datetime

I want to display all rows which is created on todays date display upto yesterdays 06:00 pm.after that the content will be refreshed based on based on a DATETIME field called 'created' that holds the date and time of each record's creation.

this is my query to fetch rows but it is not working properly...plz help me......

$sql = "
    SELECT name
    FROM pagination where created >= DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
       ";


texts: What is the signifigance of a fillfactor of 20?

texts: I have a database for our ticket system in SQL Server express.  I was poking around and noticed that a lot of the indexes have a fillfactor set to 20.  What is the signifigance of it being set to 20?  Why would you set it so low?