texts: Follow up to: SQL Server 2008 R2: Problems after computer name change

texts:
The Original question can be found here:

SQL Server 2008 R2: Problems after computer name change

I need to know if there is a way to modify the script found in the original question to allow for all instances on a computer. I have a situation where I have used an image to duplicate computers and the instances are keeping the original name of the computer.

I can fix it by manually renaming each instance but that takes time and there are 5 instances at least on at least 15 computers. It would be great if there was a way to script it to look at each instance and check the current name of the computer and correct each instance.

Here is an example of the current state of one of the computers:

The first section is the dialog when you start up the management console. Here I have to change the Server Name and the login so that it matches the current computer.

The second section is the place where I can rename the user for the instance in the security section of the instance.

The third section is the Computers Local Security Groups list. As you can see there is a whack of groups for each program that installed an instance and the names are all of the original computer name. I am concerned that this is a problem that will come back to bite me later and I was wondering if I need to make corrections to these group names. Yes the computers' name was originally IMW761 then changed to IMWTechTemp, but not before I had made my Acronis image to duplicate the computer IMW761.

I found a script in the other thread I mentioned, I was hoping that there was a way to modify it and make it do it for all SQL instances on the computer. This is the script:

declare @currentName as nvarchar(128)
declare @newName as varchar(max)
declare @serverName as varchar(max)
declare @serverInstance as varchar(max)

select  @currentName = @@SERVERNAME
select @serverInstance = cast(serverproperty('InstanceName') as varchar(max))
select  @serverName = cast(serverproperty('MachineName') as varchar(max))

set @newName = @serverName

if (@serverInstance <> '')
begin
      set @newName = @serverName + '\' + @serverInstance
end

if (@currentName <> @newName)
Begin
      print 'sp_dropserver ''' + @currentName + '''';
      print 'go'
      print 'sp_addserver ''' + @newName + ''',local'
      print 'go'
end
else
Print 'ALL OK'

Any ideas, suggestions or help is greatly appreciated.


texts: Shrink logs in between user transaction in SQL Server

texts:
I have a database where there are like billions of rows and I need to delete the data based on some condition.

Initially I was trying to delete the code in one transaction and log starts growing to the hard disk and fails, then I tried to do it in chunks.

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (2000000) -- this will change
    Databasename.dbo.Tablename
    WHERE loading_time < '08/27/2014 4:00:00'

  SET @r = @@ROWCOUNT;

  COMMIT TRANSACTION;
 -- printing if transaction is commited
  print 'done'
  CHECKPOINT
-- shrinking file after transaction is comminted
  --DBCC SHRINKFILE (2, TRUNCATEONLY); -- CHECKPOINT;    -- if simple
  -- BACKUP LOG ... -- if full
END

Is this the right way to delete data and delete logs, in between user transaction because when I did it I get an error ;

  Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.

Update I have change the code to include checkpoint, but even after adding check point, after 3 hours logs get filled and full rollback takes place.
What should i do to delete ?


texts: Should we use SQL Server partitions for a multi-tenant database design?

texts:
My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server.

It has been suggested that partitioning the database by Client ID might be a good way to manage the data volume (which could be considerable, certainly billions of rows over time)

Is this a good idea? Why or why not?


texts: how to change this dynamic rows to columns

texts:
I have following columns (it is dynamic, more rows in table with same ID).Type can be duplicate.

+--------------+---------+------------
| ID           | TYPE    | VALUE     |
+--------------+---------+------------
|            1 | A     | one         |
|            1 | B     | gas         |
|            1 | C     | age         |
|            1 | D     | two         |
|            1 | E     | gas         |
|            1 | F     | age         |
|            2 | G     | env         |
|            2 | H     | mas         |
|            2 | I     | sat         |
+--------------+---------+-----------+

I am trying to get following output

ID | A  | B | C | D | E | F | G| H | I
1    one  gas age two gas age null null null
2    null null null null null env mas sat

How it is possible in Mysql


texts: Data warehousing: data transfer process too slow with larger batches

texts:
I have two primary instances of SQL Server, one for OLTP and one for reporting. The data warehouse for reporting is basically a Type 2 SCD: every table has DWDateStart and DWDateEnd columns and the row with the current data has DWDateEnd = '12/31/9999'. An SSIS package gets the new data from the OLTP instance using Change Tracking (whole row, not just changed values) and copies the data into staging tables in the reporting instance. Then a stored procedure executes in which the rows to be updated get a new DWDateEnd and the new values (i.e. current) from the new or updated rows get inserted.  Here's an example:

UPDATE A
SET DWDateEnd = GETDATE()
FROM DWOrders A JOIN StagingOrders A1 ON A.UserId = A1.UserId AND DWDateEnd = '12/31/9999'

INSERT INTO DWOrders (OrderID, ApplicationId, UserId)
SELECT p.OrderID, p.ApplicationId, p.UserId FROM [StagingOrders] p WHERE StagingIsDeleted = 0

Here's my problem:  When 30,000 rows are updated, for example, the update and insert from staging to DW can take over half an hour, whereas under normal load the whole process finishes in 40 seconds.

This happens even with tables that aren't very wide. There are lots of indexes on these tables but in my testing it seems like removing the indexes doesn't help much. I see tons of disk waits (PAGEIOLATCH_EX and SH) but my SAN is good; not SSD-type amazing, but very good. I can't redesign the database aside from trying partitioning but I worry about partitioning on DWDateEnd since a lot of rows would be moving in and out of the "active" partition all the time.

Any thoughts on what I should do to get my data updated more quickly from staging to DW?


texts: Paging through data that is frequently changing

texts:
When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted?

If I browse a catalog of 100 records with 10 per page, that is 10 pages; however, as new records are added or removed it would impact the page number the user could be on in the next page load.

I am thinking the first query would be to get a list of all ids, store those in a separate table and then do a join on that.  This way new records or ones marked as deleted would not affect this.  After a few hours I would delete these ids from the new table so they are not hanging around forever.

Is there a better way to achieve this?  Consider this just a standard relational db structure using 3rd normal form.


texts: Long Relational Algebraic expression provided than actually required

texts:
Schema Diagram:

Make a list of project numbers for projects that involve an employee
whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

The given solution is :

However, I believe that simply using the following expression should be enough:

Now, the reason why I believe this is mainly because of the fact that the EMPLOYEE relation holds the tuples which correspond to workers as well as managers. Now, an employee can be either a worker or a manager. So, going by that logic the above expression should be enough.
I wholeheartedly agree with the fact that a given query can be represented through more than one relational algebraic expressions, however doing what the given solution (the bigger one) suggests above is highly redundant.

So to sum it up, is the direction of my thought correct?

Source : Fundamentals of Database Systems by Ramez Elmasri & Shamkant B. Navathe, 6th Edition, Page No. 172 Query 4.


texts:
I'm trying to write a statement where the settled date is less than the min(duedate) but I'm getting this error:

  Msg 130, Level 15, State 1, Line 32
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is the query I'm using - can anyone help please?

...
sum(case when
             cast(l.settleddate AS date) < min(cast(f.datedue as DATE))
         then 1
         else 0
    end) as BeforeFirstDue


texts: Missing Index Not Displaying In Execution Plan

texts:
I recently installed SQL Server Management Studio 2014 (Developer Edition), and I noticed that I am no longer seeing the missing index feature.

What I have done:

Ran the workload through Database Engine Tuning Advisor, so I know that the table is missing an index.
Found nothing online about enabling the feature
Sifted through every option that seemed related.

Any suggestions would be greatly appreciated. I was previously running SQL Server 2012 Developer Edition, prior to 2014.

UPDATE

I am able to get it to display the missing index in certain cases. Take a look at these two queries:

For this query, no missing index is displaying in the execution plan. The WHERE Clause field is a varchar(512) and has no indices on this column.

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

For this query, it is showing a missing index in the execution plan. Age is an INT

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

Missing Index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

I also tried a query with a VARCHAR(500) field, and it did return a missing index. I am really scratching my head now.


texts: What strategy does MySQL optimizer use when choosing between two indexes?

texts:
In question:

Optimize a query with two range conditions

the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN, and that was the same thought that I had as well. However, that did not help, the execution plan remained the same.

table   type    possible_keys                 key
book    ref     author_id,publish_date,i0,i1  author_id

So I removed the index author_id and created a new index like:

  KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

I was a bit surprised to see the new plan:

table   type    possible_keys                 key
book    ref     publish_date,i0,i1,i2         i0

why did it not choose i2 over i0 when it did choose author_id before? I got even more puzzled when I switched places in the table for i0 and i2:

PRIMARY KEY (`id`),
KEY `publish_date` (`publish_date`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`),
KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)

Now all the sudden index i2 were chosen:

table   type    possible_keys                 key
book    ref     publish_date,i2,i0,i1         i2

Do I miss something or does the optimizer pick the first declared index that it benefits from even though there are better indexes declared later on?

With the following indexes and a query where predicates involve author_id, country, org, publish_date, price:

KEY `author_id` (`author_id`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

I would expect the optimizer to choose i2 over author_id, but if I get it right it depends on the order they are defined.

Are these observations true in general or does it only happen for small tables?


texts: Bounding Column's Values w/ Foreign Key Relationship?

texts:
I'm creating a new table that contains a 'user'. So far, the table looks like:

create table users(
  id primary key,
  code varchar
)

Currently, the "universe" of codes consists of: A or B.

Is it worthwhile, from a DB Design point of view, to create a codes table that contains all known codes?

create table codes(
  value varchar, -- with unique constraint
  description varchar
)

Then, I could update my users table's code column to have a foreign key relationship to the codes.table column.

Does this change seem to be useful for my above case?


texts: MySQL select returns no rows while there are many

texts:
I'm using django to create a database and I manually created a test values in the database by creating objects and saving them to the database.
Now when I run this query:

select TABLE_NAME,
       TABLE_ROWS
from information_schema.tables
where table_schema = 'test_test_db';

It returns the tables with the correct number of rows. For example, a table 'accounts_users' has 4269 rows.

But when I try to select any values using select username from accounts_users;, I get 0 rows back or 'Empty Set' result.

And of course my django app cannot run because there is "no" data in the database.

What is wrong?


texts: Startup oracle database 12c manually with SQL*Plus command not when OS loads

texts:
I changed oracle instance to manually start from services.msc (service name is "OracleServiceORCL"), Because I want to start it up manually with SQL*Plus commands whenever I want, not with OS load.

Now I can't do that in SQL*Plus and I got this error:

  ORA-12560: TNS:protocol adapter error

So I start it manually from services.msc and when database got ready I shut it down with SQL*Plus command. After that I realize that the service is still running, with very low consuming of resources.

I think this called "idle instance".

How can I automatically be in an idle instance state after I logged in to my Windows 8.1 to start oracle database whenever I want with SQL*Plus commands, not manually from services.msc?


texts: Comparing nullable column values using "IS NULL" versus "= NULL"

texts:
I'm dealing with nullable values in a column of a table, i'm wondering why this:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 is null

returns a result set with the two "rows" having a null value in c2; comparting to this query:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 = null

that returns an empty result set!!

so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.

P.D: This question also applies to the case is NOT NULL versus <> NULL


texts: View references materialized for simple MySQL VIEWs?

texts:
Original question: If I have a view: CREATE VIEW foo AS SELECT * FROM bar WHERE bar.deleted = 0 are there any queries which perform slower ie. SELECT ... FROM bar ... WHERE bar.deleted = 0 AND ... vs SELECT ... FROM foo. In yet another way, is it always just a simple query rewrite?

This is a generic question and I do not have a specific table to show because I am trying to help with designing a framework.

Edit: the MySQL handbook has this under 9.2.1.18.3:

  As of MySQL 5.7.6, the optimizer handles derived tables and view references the same way: It avoids unnecessary materialization whenever possible, which enables pushing down conditions from the outer query to derived tables and produces more efficient execution plans. Before MySQL 5.7.6, derived tables were always materialized, whereas equivalent view references were sometimes materialized and sometimes merged. This inconsistent treatment of equivalent queries could lead to performance problems: Unnecessary derived table materialization takes time and prevents the optimizer from pushing down conditions to derived tables.

Does anyone have examples where view references were sometimes materialized with the simple VIEW definition I've given (all fields select from a single table with a constant filter on a single integer field)? Or that only happened with more complex VIEW definitions? That is exactly what I am after.


texts:
Being a novice Postgres user, I have a table ad in my PostgreSQL 9.5 (x64) database with 87 rows. In addition to other columns, it has two columns 'start' and 'end' having date-time duration range like this:

ID      Start                   End
1       2003-06-07 00:00:00     2004-09-30 23:59:59

I need to split the range into one year windows in separate rows (from start year to the last year of interval) stored in database like this:

ID       Start
1_2003   2003-06-07 00:00:00     2003-12-31 23:59:59
1_2004   2004-01-01 00:00:00     2004-09-30 23:59:59

Using the operator ||'_'|| and Extract() function, I am able to concatenate ID with year. Also, this question addresses how to split up in interval in weeks and this one shows how to do the same for days but none of them addresses how to split up an interval in years specifically.

I avoided this question because I don't want to go for a stored procedure based approach. I am aware that generate_series() returns a series from start and stop parameters but actually I am struggling to break the interval at last day of the year and then re-starting from first day of the year in next row. I would highly appreciate if someone could guide me to this?


texts: Why SQL server unload CLR assembly every midnight

texts:
Every midnight not a specific time, the sql server will unload the CLR assembly. The reason is that due to memory pressure, the assembly is marked with unload and will be unloaded soon.

How to make the assembly never unloaded, since reload will impact the query performance a lot.
Second question is how to find the root cause of sql server reported high memory pressure at midnight?

I was using the performance monitor to track the memory usage of each OS processes, and didn't see the huge memory usage increase at that time.

The system is Windows Server 2008 R2 and SQL server 2008 R2.

The system is x64. Sql server is x64 standard.

The log is "AppDomain 9 ( is marjed for unload due to memory presure.)"

I don't think there is a schedule job at that time.

The reason of why the memory pressure is high at that time is unknown.


texts: How to set up the SQL Server to read a database from external hard disk or a different hard disk from my PC

texts:
Is it possible for SQL Server to access a database from a different location? My Primary Hard Disk (C:) is almost full, however I have a second Hard Disk in my computer.

When I try to restore the database, SQL Server says

  There is insufficient free space on Disk C:// to create Database

Is there any way to move from C:\ to D:\?


texts: SSIS Package not retaining parameters in SQL Server agent job

texts:
I have an SSIS package stored in the Integration Services catalogs on a SQL server instance.

This package is used as a step in a SQL Server agent job.

The package has 4 required parameters.

Using SSMS on my local machine if I edit the package I can add all of these parameters and the job runs fine.

However editing the step from SSMS on the server gives the following error:

You can enter this value but every time you return to edit the step the error re-occurs and the parameter values are blank.

Because the person deploying this package will have to use SSMS on the server I could really do with working out what is causing this.

Server
SQL Server: SQL Server 2012 (11.0.3128)
SSMS: 11.0.3128.0
OS: Windows Server 2012 (6.2.9200)

My PC
SSMS: 11.0.3128.0
OS: Windows 8.1 (6.3.9600)


texts: Insert 4 tinyint values into varbinary(4)

texts:
I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form.

Right now, I have this:

@stringIP = '192.168.0.3'
select
    CAST(PARSENAME(@stringIP, 4) as tinyint),
    CAST(PARSENAME(@stringIP, 3) as tinyint),
    CAST(PARSENAME(@stringIP, 2) as tinyint),
    CAST(PARSENAME(@stringIP, 1) as tinyint)

This breaks up my IPv4 Address very well; but I need to figure out how to insert it into a varbinary(4) so I can insert the data to my table. I cannot find ANY TSQL syntax to combine these four tinyints into a varbinary(4) -- I can only find C#/VB examples using paramterized SQL. This is fine and good, but I'm trying to create an SQL Function do use in an INSTEAD OF INSERT trigger.


texts: Advice on how to approach data cleaning & importing

texts:
I am importing and cleaning data from a datasource that have records like this:

The data needs sorting and rearranging based on the Event Type.

The destination schema would be something like this:

Create Table dbo.Destination
(
DestinationId int not Null primary key clustered,
EventType int not null,
fkyCustomerId int not null,
         Constraint FK_Destination_Customers References dbo.Customers (pkyCustomerId),
fkyCategoryId int not null
         Constraint FK_Destination_Categories References dbo.Categories (pkyCategoryId),
fkyCompanyId int not null
         Constraint FK_Destination_Companies References dbo.Companies(pkyCompanyId),
fkyLocationId int not null
        Constraint FK_Destination_Locations References dbo.Locations(pkyLocationId),
Amount money not null
        Constraint DF_Destination_Amount Default(0)
)
-- Lookup table
Create Table dbo.Categories
(
pkyCategoryId int not null primary key clustered,
SourceCategoryId char(10) Not Null,
CategoryName varchar(50) Not Null
)
-- Lookup table
Create Table dbo.Companies
(
pkyCompanyId int not null primary key clustered,
SourceCompanyId varchar(10) Not null,
CompanyName varchar(50) Not Null
)
-- Lookup Table
Create Table dbo.Locations
(
pkyLocationId int not null primary key clustered,
SourceLocationId varchar(10) Not Null,
LocationName varchar(50) Not Null
)

For each Event Type, and there could be hundreds of event types, there are rules on what data exists in each of the col columns.

So for Event Type 1234, Col0 has the mapping for the Categories lookup and Col1 has the mapping for the Companies lookup.

However, for Event Type 5000, Col0 contains the mapping for the Companies lookup and Col1 contains the mapping for Locations.

Also, there are situations when there will be null values for all Col columns which means I need to map these to default category, company etc values.

I need to design an import process that can handle the existing rules and potentially new rules. The import will handle approx 2.5 millions rows per day and increasing.

What would be a good way to implement this kind of import. Nothing is set in stone here, apart from the the source data, so I am more than willing to entertain the idea of changing the schema etc.

The final goal is to transform the source data so it can be easily used for reporting purposes.


texts: Constraint to only allow unique value in a group

texts:
I have a table in PostgreSQL 12 with columns "sample", "group", "value". There can be multiple samples in a group. Is it possible to implement a constraint so that each group can only have one distinct value?

EDIT:
This is for a rather complex materialized view, where I would like to make sure that there are no samples in a group that has a value that differ from the other samples. In that sense "value" should be a property of the group in this specific context, but that isn't always the case in the database. The use case is that samples are biological samples taken from different tissues ("value") and groups are e.g. "treated" and "control", and I would like to make sure that there aren't a mix of tissues within one group.

This should be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value1'), 'sample3', 'group2', 'value2'). Here there are two samples in group 1, but they both have the same value.

This should not be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value2'), 'sample3', 'group2', 'value2')

This should fail because the two samples in group 1 have different values.

I think some of the confusion came from that I used "unique" in the original post when I was talking about distinct values, but this has nothing to do with UNIQUE. For this to work a CHECK constraint for a row being added would depend on several other existing rows. I don't know if that works? Or if there should be a trigger maybe? I guess there could be an issue with race conditions on bulk inserts?


texts: How to remotely run commands for a DB2 instance, preferably using ibm_db?

texts:
My objective is to see the size of Audit Buffer size, normally I would use below command on my Db2 instance

db2 get database manager configuration | grep AUDIT_BUF_SZ

But now I need to do it remotely over an Ubuntu 18 system which has my Db2 instance. For SQL query execution I used ibm_db library, an example of which is below

select="select * from department"
stmt_select = ibm_db.exec_immediate(ibm_db_conn, select)
cols = ibm_db.fetch_tuple( stmt_select )
print(cols[0])

So I need to know is there any way of getting the output of my command that can be implemented through Python, else any other library that can come in handy for this.


texts:
This SQL code returns "a" instead of "asdfg":

CREATE FUNCTION [dbo].[GetPayload2] (@ID int) RETURNS VARCHAR(300) AS
BEGIN
IF @ID = 1
RETURN 'asdfg'
RETURN ''
END
GO
SELECT dbo.GetPayload2(1)

Reproduced on both 15.0.2000.5 and 15.0.2070.41. Works fine in SQL Server 2017.
Workaround is to use WITH INLINE = OFF.

EDIT:
If you agree it's a bug, vote here please:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39190126-sql-server-2019-scalar-functions-with-inline-are-b

EDIT 2:
CU2 for MS SQL Server 2019 is solving this issue :-)


texts: Strange sql behavior - last character is removed in the Select statement, but actual data has different length

texts:
We have a table with a column name A with type nvarchar(23).

following query will always return 23 which means that the actual length of all records are 23.

select length(trim(req.A)), count(*)
  from tableName req
group by length(trim(req.A));

|length(trim(req.A))|count(*)|
------------------------------
|23                 |1006    |

But when we select from this table with following query it behaves different and it seems that the last character is always removed in result Gridview in the pl/sql developer.

select LENGTHB(req.A) lenb, length(req.A) len, req.* from tableName req
where req.A = 'NHBBBBB1398052635902235'; -- Note to the equal sign and the last charactar (5) of the where clause

the result is:

|lenb|len|          A           |
---------------------------------
|46  |23 |NHBBBBB139805263590223|

As you can see the last character (5) is removed in the select result.

Also when we rewrite previous query with to_char() or run it in sqlplus it works fine & return correct result with the length of 23 and (5) as the last character.

select LENGTHB(req.A) lenb, length(req.A) len, to_char(req.A) from tableName req
where req.A = 'NHBBBBB1398052635902235';

the result is:

|lenb|len|    to_char(req.A)     |
----------------------------------
|46  |23 |NHBBBBB1398052635902235|

Can you please explain whats happen!? Is this related to pl/sql configs? How to solve this?


texts: Need help designing table with list of IDs to store

texts:
I have a table that I need to create to hold saved emails for FUTURE delivery (to, from, message, scheduled send date, etc).

The catch here is that I don't know who's supposed to receive the email until the day of delivery.

What I mean is, the email is created to go to certain selected organizations, but the "members" of the organization will be constantly joining and leaving, so if someone creates an email for delivery next month, the list of member email addresses to send to by then will be different.

So, what I need to save in the table is the list of organizations that the email should go to, so that I can query for the latest list of member email addresses when I actually send the email.  Hope that makes sense.

Anyway, so my question is: what is considered a "proper design" for this?

My initial thought is to just save a comma delimited list of organization IDs.

I know I will never have to search on which organizations were on the list, so I don't care if it's not query-able, and I know I could normalize it into one row per recipient organization, but it seems such an unnecessary repeat of data for no purpose, especially since I only query on the SENDER not the recipients.

So is a list of IDs just a horrible, no good, only-a-newbie-would-think-of-that, bad thing? Or can it be used in some cases? Or is there some other way to do this that I don't know about? I'm sure I can't be the only one who's run into a situation like this before!


texts: Can I make a full export of a corrupted database?

texts:
I have a datafile with a corrupted block. Unfortunately I don't have backup to repair with RMAN (BAD strategy, first the strategy delete the old backup and after make a new backup) Because this new corruption I can't make a new backup with RMAN..

What happen if I make a full export of the database? the export will fail as well? Can I filter for don't make the export of the data in that block?


texts: Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

texts:
I create a table type in SQL:

CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
    [Name] VARCHAR(MAX) DEFAULT '',
    [Index] VARCHAR(MAX) DEFAULT ''
)

I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

// Set up connection and command variables (code omitted)
// ...

// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");

// Add one row that is missing an Index
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);

// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType",
    Value = table
};

command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();

I expected that inside of the stored procedure, the index value would default to '' (empty VARCHAR). Instead, I observed that the value was null. I know this because I attempted to merge the table type into an actual table, and the index column on my table does not allow nulls.

Why doesn't SQL Server honor the default that I placed on my table type? Is there a way to force SQL Server to honor it?

I think I can get around the issue by using the DataTable.DefaultValue property, but that solution has the disadvantage that I need to declare same defaults in two different places. I want to avoid redundant code if possible.


texts:
I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.

CREATE OR REPLACE FUNCTION toned.function_validate(varmember tbl_member)
  RETURNS text AS
$BODY$
...

The definition of tbl_member below

CREATE TABLE toned.tbl_member
(
  code smallint NOT NULL DEFAULT nextval('toned.seq_code'::regclass),
  name text NOT NULL,
  email text,
  address text,
  city text,
  country text
);

can anyone help me to loop through the variable? I dont know how to
 refer to the field names.
can i do something like varmember[0] ??


texts: I have a large mySQL database and I am replicating to two slaves.  All three servers are windows (i know).  One of my slaves seems to have corruption, the other is fine.  This is huge DB, a few hundred gigs.  I am trying to figure out the best way to fix the replication on the one slave that is corrupted.  I need to minimize downtime to the master so a mySQLdump seems problematic unless its the only way.  I was wondering if because I have two slaves and I am pretty sure all 3 environments are close to identical there might be another creative way to solve this.  It is InnoDB but might I be able to shut all three down and do a directory copy of the good slave (datadir) and use it on the bad slave, restart?  I think that might only take a few hours as opposed to a mySQLdump.  Any thoughts on how to accomplish this correctly would be helpful.


texts: Create a column in a table based on the rest of the row and other table

texts:
I have two tables, and I'd like to select a range of positions from one table for each row in the other table.

I've tried to relate the tables in order to get new information but it seems like there is no OUTER APPLY in MS Access. Here is my code:

    SELECT *
FROM [pred-predi_calculo-presi]
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta < [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta DESC
    ) AS p
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta >= [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta ASC
    ) AS n ;

I thought it'd be much easier to do this action with Java code (I have the algorithm in mind) and also more optimal, but I don't have the right tools like data structures or right "commands" to work with databases through Java (I'm a mathematician) and thus would need much more help. (Although I'm open to suggestions about the Java option.)
 Here's a sample data:

pred-predi_calculo-presi:

Id1 ID                       Paradero'horaPrediccion''pasadaReal'PREDICCION_NUEVA   PATENTE servicio
1   2016-12-13 17:36:50_T518    PA52    17:36:51       17:36:20      1579,242       CJRW-73    518
2   2016-12-13 17:36:50_T518    PA52    17:36:51       17:36:20      761,211        BJFB-79    518
3   2016-12-13 17:11:02_T518    PC475   17:17:53       17:16:34      1382,545       BFKC-47    518
4   2016-12-13 17:11:02_T518    PC475   17:17:53       17:16:34      695,283        CJRW-74    518
5   2016-12-13 17:11:03_T418    PC206   17:16:27       17:15:50      1210,033       ZN-6742    418
6   2016-12-13 17:11:03_T418    PC206   17:16:27       17:15:50      1407,741       ZN-5814    418
7   2016-12-13 17:16:41_T516    PD208   17:17:56       17:18:02      842,634        BJFY-17    516

snptc_histo:

PATENTEBUS  POSICIONBUS FECHAGPS
WA-9114     0           17:15:52
CJRW-83     3,64        17:15:52
ZN-3967     0,26        17:15:50
BJFV-89     11,06       17:15:51
BJFS-92     32,866      17:15:51
FLXJ-30     19,74       17:15:51
BJFR-52     9,55        17:15:50
CJRW-52     24,67       17:15:50
BJFR-94     14,75       17:15:49
CJRT-63     16,02       17:15:49


texts:
I have a VPS in GoDaddy, which is crashing MySql service at frequent times.

Here is the log file that may help to help me.

160425 09:24:53 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]
/usr/sbin/mysqld[0x59c2bd]
/usr/sbin/mysqld[0x6ea5d1]
/usr/sbin/mysqld[0x972d7d]
/usr/sbin/mysqld[0xa2ad41]
/usr/sbin/mysqld[0xaad67a]
/usr/sbin/mysqld[0xaaba64]
160425 09:24:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160425 09:24:47 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]
/usr/sbin/mysqld[0x59c2bd]
/usr/sbin/mysqld[0x6ea5d1]
/usr/sbin/mysqld[0x972d7d]
/usr/sbin/mysqld[0xa2ad41]
/usr/sbin/mysqld[0xaad67a]
/usr/sbin/mysqld[0xaaba64]
160425 09:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160425 09:24:41 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]

mysql has failed. Contact your system administrator if the service does not automagically recover.

Please help me to recover it!
Thanks.


texts: Defining constraints in `CREATE TABLE` statements

texts:
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the CREATE TABLE statement.
Whilst taking Stanford's "Introduction to Databases" MOOC, the SQL Standard was mentioned as supporting any query within the CREATE TABLE statement as constraints (essentially replacing a major use-case for triggers).
What is best practice?
Below is a simple example of including constraints in CREATE TABLE statements; rather than through ALERT TABLE and/or CREATE TRIGGER statements:
CREATE TABLE Place (
    address VARCHAR2(40),
    CONSTRAINT place_pk
        PRIMARY KEY (address)
);

CREATE TABLE Company (
    c_name VARCHAR2(40),
    CONSTRAINT company_pk
        PRIMARY KEY (c_name)
);

CREATE TABLE Employee (
    e_name VARCHAR2(40),
    tax_no NUMBER,
    salary NUMBER(19,4),
    sex CHAR,
    birthdate DATE,
    address VARCHAR2(40),
    CONSTRAINT employee_pk
        PRIMARY KEY (tax_no),
    CONSTRAINT address_fk
        FOREIGN KEY (address) REFERENCES Place(address),
    CHECK (address IS NOT NULL)
);

CREATE TABLE CompanyEmployee (
    employee_id NUMBER,
    company_id VARCHAR2(40),
    CONSTRAINT unique_employee_id
        UNIQUE(employee_id),
    CONSTRAINT employee_id_fk
        FOREIGN KEY (employee_id) REFERENCES Employee(tax_no),
    CONSTRAINT company_id_fk
        FOREIGN KEY (company_id) REFERENCES Company(c_name),
    CONSTRAINT company_employees_pk
        PRIMARY KEY (employee_id, company_id)
);

BTW: You'll note that I'm using CAPS for keywords, upper CamelCase for table names and lower under_score for attribute and trigger names. Is this good practice? - Feel free to critique my indentation and whitespace usage styles also :)


texts:
Now , I read the document about "Transaction ID Wraparound " , but there are something that I really
don't understand, the document is the following url
http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

23.1.4.  Preventing Transaction ID Wraparound Failures
PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

I don't understand the statements "would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible"
Can someone explain this ? Why after the database suffers transaction ID wraparound would transactions that were in the past appear to be in the future ? In short, I want to know if the PostgreSQL will in  the " data loss" situation after transaction ID wraparound by autovacuum。
For my personal views, we can get the current transaction ID by using txid_current() function whoes output is 64 bit and will not be cycled.So I think the Insertion transaction ID  of tuples which knows as xmin will nerver greater than the xid which get by txid_current() function. Except that  you will use pg_resetxlog reset reset transaction ID after shuting down PostgreSQL Server. Am I right ? Thanks


texts: SQL or NoSQL for structured clinical data

texts:
I'm designing a database for a primary health center, the idea

We basically have a Patient with many Encounters, also the Encounters have to be entered from different Users (Health Professionals). Patient info is unlikely to change, the Encounter is the "important" part of this database, already persisted data it's not going to be modified, as we only have to insert new encounters as the patient comes to their controls. Users info it's also (very) unlikely to change. It could be possible that we are going to need to add more columns once a year in case the Health Ministry decides we need to start "logging" something else.

Right now, the "Encounters paper" (tarjetón) is basically a table with around 20 columns, where each row is a new Encounter.

This is a small center, so I'm not going to be dealing with lots of data, concurrency won't be an issue either, since only a couple of professionals are going to "write" to the database.

I started building using mongodb (rails app, using mongoid)


texts: What database name should I use to connect Tomcat with my database

texts:
I have a dump file in Oracle and I should use Oracle 12c.

I installed it, created a database and imported the dump into a tablespace,
but I can't connect it with my application on Tomcat.

I'm lost between all the names in Oracle 12c. What username should I use to configure the Tomcat server.xml?


texts:
From BOL, my understanding is that DBAs do not need to administer DLLs created for memory optimized tables, or natively compiled stored procedures, as they are recompiled automatically, when the SQL Server service starts and are removed when no longer needed. But I am witnessing, that even after a memory optimized table has been dropped, and the service restarted, the DLLs still exist in the file system AND are still loaded into SQL memory and attached to the process. This can be witnessed by the fact that they are still visible in sys_dm_os_loaded_modules, and are locked in the file system if you try to delete them, whilst the SQL Service is running.

Is this a bug? Or are they cleaned up at a later date? If at a later date, what triggers the clean-up, if it isn't an instance restart?


texts: Can I use WHERE IN when I have combined primary key?

texts:
So if I have a table with primary key consisted of one id, I can update multiple rows by writing

UPDATE tableA SET colA = 'newValue' WHERE id IN (1,3,5,7,9)

Can I do it somehow if I have primary key consisted of 2 columns? I would like to update multiple rows providing some kind of array of keys.


texts:
I'm download this version mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz. I would like to make a specific install for example :

basedir = /sgbd/mysql/5.6/mysql
datadir = /data/mysql/MYSDTESTP02
socket = /sgbd/mysql/5.6/mysql/conf/MYSDTESTP02.socket

I have done this :

./scripts/mysql_install_db --user=mysql --defaults-file=/sgbd/mysql/5.6/mysql/conf/my.cnf --keep-my-cnf --no-defaults

cp /sgbd/mysql/5.6/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on
systemctl daemon-reload

./bin/mysqld_safe --defaults-file=/sgbd/mysql/5.6/mysql/conf/my.cnf --log_error=/data/mysql/[instance_name]/mysql_error.log --socket=/sgbd/mysql/5.6/mysql/conf/TESTP02.socket &

./bin/mysql_secure_installation --user=mysql --defaults-file=/sgbd/mysql/5.6/mysql/conf/my.cnf --keep-my-cnf --socket=/sgbd/mysql/5.6/mysql/conf/TESTP02.socket

systemctl restart mysqld

it's ok but when i restart the service of mysqld, i have a socket on /tmp/mysql.sock and the /sgbd/mysql/5.my.cnf is ignored

ps : i have edit the mysqld on /etc/init.d/mysqld

line  46 basedir=/sgbd/mysql/5.6/mysql
line  47 datadir=/data/mysql//[instance_name]
line  48 socket=/sgbd/mysql/5.6/conf
line  68  basedir=/sgbd/mysql/5.6/mysql
line  69  bindir=/sgbd/mysql/5.6/mysql/bin
line  72  datadir=/data/mysql/[instance_name]
line  74  sbindir=/sgbd/mysql/5.6/mysql/bin
line  75  libexecdir=/sgbd/mysql/5.6/mysql/bin
line  209 conf=/sgbd/mysql/5.6/mysql/conf/my.cnf


texts: Manual Binary logging in Amazon's Aurora

texts:
How would I enable binary logging in RDS? I get the following error:

Binary logging is disabled for MySQL server [1020418]  (mysql_endpoint_capture.c:352)

What parameters do I need to change in order to get binlogging on? My parameters from a query are:
> show variables like '%log_bin%'

log_bin OFF
log_bin_basename
log_bin_index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events   OFF
sql_log_bin ON


texts: Is it harmful to update columns which aren't changed?

texts:
Say I have a table with some columns:

CREATE TABLE t
(
    key int PRIMARY KEY,
    c1  int,
    c2  text,
    c3  timestamptz
);

I now fetch a row from the table:

SELECT * FROM t WHERE id = 180;

In my application, I now only change the value of one column, let's say c2. Is it harmful performance-wise to do:

UPDATE t
SET    t1 = <exact the same value as before>,
       t2 = <new value>,
       t3 = <exact the same value as before>
WHERE  key = 180;

instead of:

UPDATE t
SET    t2 = <new value>
WHERE  key = 180;

?


texts: Automating performance tests in PostgreSQL

texts:
I want to check the performance of specific queries in a Postgres database on a regular basis. Also, I want to be able to check the change in performance of different queries with any change in my Postgres configuration or database schema/indexes.

So far I have tried pgbench, but feel like I need to automate the process of passing the script pgbench will use to test. But pgbench tests my configuration by generating its own tables and those tables will not reflect the level of joins I might be using on real data.

I'm using Postgres 10.0


texts: Incorrect syntax near the keyword 'is' in SQL server vb6

texts:
Set rs = con.Execute("select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all      where rw=0 and iif(month(bill_date)&gt;=1 AND month(bill_date)&lt;=3,year(bill_date)-1,year(bill_date))=" &amp; IIf(Month(Date) &gt;= 1 And Month(Date) &lt;= 3, Year(Date) - 1, Year(Date)))

how to change vb6 code (MS access) database to SQL Server 2012?


texts: What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression?

texts:
I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space)  pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this cause the important thing is that there might by more that one space before and after each AND .
I've written two different queries for this
 query1:

 select regexp_replace('x=y AND z=y AND p=q','[[:space:]]AND[[:space:]]','*')
 from dual;

query2:

 select regexp_replace('x=y AND z=y AND p=q', '\s+AND\s+', '*')
 from dual;

I want to know :

Are there better ways to do that?
What is the main difference between these two queries? I mean[[:space:]] and \s? The important thing is that I want to know In what sort of condition these two queries will produce different result?

Thanks in advance


texts: Is there a way to explicitely have a not-null column in a view

texts:
If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table:

create table tq84_t (
  col_1   number,
  col_2   varchar2(15) not null
);

create view tq84_v as select col_1, col_2 from tq84_t;

desc tq84_v;

will print

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2           NOT NULL VARCHAR2(15)

Now, if the view's select statement contains a union all, it seems as though that characteristic is not inherited anymore:

drop view tq84_v;

create view tq84_v as
  select col_1, col_2 from tq84_t where col_1 < 10 union all
  select col_1, col_2 from tq84_t where col_2 > 20;

desc tq84_v;

prints:

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2                    VARCHAR2(15)

This is a bit unfortunate for my purposes. So, is there a way to explicitely state that a column be not null for a view in Oracle?

Note: I obviously used a simplified view defintion to demonstrate the problem, and I could re-write the view definition without using union all. Yet, my actual view defintion is much more complicated, requiring the union all.


texts: How to understand I/O bottleneck for SQL Server

texts:
I'm getting into the dba world for SQL Server via "trial by fire", and I have a IO question.

I have a SQL Server 2016 engine built upon Windows Server 2012.  This setup includes:

128Gb Ram
6Gb/s Megaid SAS controller

4 x 600Gb 15KSAS drives in RAID 5 on a 2-backplane configuration with 6Gb/s connection:

2 x 1Gb Network Connection (Fiber I believe)

So here's my question:

if everything is connected via 6Gb/s SAS connections, why is my IO only at the 5 to 6Mb/s speed resulting in a continuous PAGEIOLATCH_EX suspension?  This is where I'm confused.  This includes SQL Server Read/Write, and Operating System negotiation.

Any ideas on how to figure this one out would be greatly appreciated.

Thank you very much.


texts: Changing PostgreSQL port using command line

texts:
My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).

I saw this solution before: https://stackoverflow.com/questions/187438/want-to-change-pgsql-port

I tried running the following command, but it failed:

postgres --p 5431

Could someone tell me how to change port in using command line?


texts: When using a master plus multiple slaves, how do I change over from one server to a brand new one with minimal downtime?

texts:
In my particular case, I'm using Heroku Postgres (repackaged Amazon EC2 instances running PostgreSQL), but platform-agnostic answers are also good!

Heroku's instructions
describe the process for transfering over a single database (i.e. without slaves):

You have an existing server, A.
Create server B as a follower of A.

$ heroku addons:add heroku-postgresql:ronin --follow HEROKU_POSTGRESQL_AUBURN
... Attached as HEROKU_POSTGRESQL_BLUE

Wait for B to be up-to-date with A.
Disable the application so no new writes are made to A.
Wait for B to fully catch up.
Sever the connection between B and A, so B is an independent db.

$ heroku pg:unfollow HEROKU_POSTGRESQL_BLUE

Reconfigure the app to use B as your database.

$ heroku pg:promote HEROKU_POSTGRESQL_BLUE

Re-enable the application.
Destroy A.

$ heroku addons:remove HEROKU_POSTGRESQL_AUBURN

This all works fine if you have a single database, but what happens when you have slaves?  Heroku's docs only say that you'll have to re-create the slaves, without going into further depth.  (n.b. Once you've done the process above, you can't immediately create the new slaves.  The server needs a few hours before it can be forked/followed.)

Now, if your app can handle ditching the slaves entirely, then you can do the following:

disable the slaves
change over the master server
create new slaves
enable the new slaves

But if reducing your server farm to just a single server is going to cause the app to crash and burn, you need a way to keep your slaves up and running throughout this whole process!

My first thought was to create a server C, following B, even while B is still following A.  When switching over, B becomes the new master and C the new slave.  But, alas, Heroku (and presumably also PostgreSQL) doesn't support followers-following-followers.

QUESTION: How do you change over from one whole master/slave set to another set?  How do you change over your master without having to run without slaves for an extended period of time?

And just in case it's note clear: I'm not asking about failing over to an existing slave.  I want to upgrade all of the servers to a different-sized EC2 instance.  And I want to maintain my master/slave setup throughout the process.


texts: Repopulating PostgreSQL database with data directory from another database Instance

texts: I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling PostgreSQL, I receive all the same tables and schemas from my prior database, but not the same data. The data directories are two separate, but I want to combine the previous data directory into the new one. How would I go about such a process, if possible?


texts:
I cannot understand why F1 is not equivalent to the other sets of dependencies below given the relation R(A,B,C):

F1 = {A → B, B → C}
F2 = {A → B, A → C}
F3 = {A → B, AB → C}

This is question no. 6 in this open exercise from stanford.edu, and the answer provided at the bottom of that page states that F1 is  not equivalent. But I do  not understand why because I was thinking that it is equivalent to the other sets by virtue of transitivity. I thought B → C in F1 and A → C in F2 are the same since A → B.


texts: Find substrings within between 2 string fragments

texts:
I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code.

CREATE OR REPLACE VIEW vw_actions AS
 SELECT ls.f_table_schema, ls.f_table_name,
 (SELECT substr(ls.attribute_actions_text,
 strpos(ls.attribute_actions_text, 'name="')+6,
 strpos(ls.attribute_actions_text, '"/>') -
 strpos(ls.attribute_actions_text, 'name="'))) AS actions
   FROM layer_styles ls;

The outcome is that it doesn't like minus numbers when using strpos. I can get it go forward 6 characters to remove the 'name="' from the returned substring but cannot remove the '"/>'.

It returns the following:

View SHED Database"/>

where I want it return:

View SHED Database

Any suggestions would be greatly appreciated.

ADDITION:I have found out that if I was using 9.1 I could have used strposrev and i think the following code would have worked:

CREATE OR REPLACE VIEW vw_actions AS
 SELECT ls.f_table_schema, ls.f_table_name,
 (SELECT substr(ls.attribute_actions_text::text,
 strpos(ls.attribute_actions_text::text, 'name="'::text)+6,
 strposrev(ls.attribute_actions_text::text, '"/>'::text)+3 -
 strpos(ls.attribute_actions_text::text, 'name="'::text))) AS actions
   FROM layer_styles ls;


texts:
I would like to make a backup of a postgres database file that I created on Ubuntu 16.04.

I cannot find the file. Some other sources indicate it should be under:

/var/lib/postgresql/[version]/data

When I run show data_directory inside of postgres it shows:

/var/lib/postgresql/9.5/main

However there is no data folder there.


texts: How to call a stored procedure in an Oracle Scheduler job

texts:
I want to call a stored procedure in an Oracle Scheduler job. My procedure and job are shown below:

Stored procedure

create or replace PROCEDURE emp_test
IS
begin

delete from  emp;
insert into emp(empid,empname)
select empid1,empname1 from emp_temp;
end;

Job

BEGIN
dbms_scheduler.create_job (
job_name => 'emp_test_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
CALL emp_test();
END;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY');
END;

I am able to call procedure like CALL emp_test() with out using a job.

When I am using same thing in a job like above, the job was created successfully but the procedure is not running, rows are not inserted. I need run this job and rows should be inserted; can any one help me please?


texts:
This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid?

'select DISTINCT v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 order by v.purchasedate'


texts: Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

texts:
One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works.
Does the publisher have to wait on the replication completing before transactions are committed or does it make use of a queue or the transaction log before applying changes to the subscriber?
The subscriber is used as the back-end for a custom report builder (amongst other uses) which cannot make good use of indexes so tables are frequently locked for several minutes. I am concerned for any effects this might have on our publisher, which is on our live production server.
Both publisher and subscriber are in Full Recovery.


texts: "lock request time out period exceeded" Error When Trying to See DB Hierarchies

texts:
I'm having problems with a database.

I can run basic queries, albeit much slower than normal.
When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded.
My SSRS reports that run on objects in this database are no longer completing.
Jobs associated with procedures stored on this database also do not run.

I tried using sp_who2 to find and kill all connections on the database, however this has not solved the problem.

What is going on here? How can I resolve this?


texts: "FOR UPDATE" in an "EXISTS SELECT" statement

texts:
Is there any potential problem in using (in a transaction)

SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )

i.e. having SELECT ... FOR UPDATE within an EXISTS?

MySQL does certainly some optimizations for EXISTS since it doesn't have to actually retrieve the data, and, just in case, was wondering if the row where id=36 will be locked anyway, as expected?


texts:
My query looks like this

select employees.PK_worker,
       concat(employees.name,' ',employees.surname)                    AS name,

       (select coalesce(sum(employees_to_hand.cost),0)
        from   employees_to_hand
        where  employees_to_hand.PK_worker = employees.PK_worker
        and    employees_to_hand.reg_date between @dstart and @dend)   AS to_hand_cost,

       (select coalesce(sum(employees_delegations.cost),0)
        from   employees_delegations
        where  employees_delegations.PK_worker = employees.PK_worker
        and    employees_delegations.dstart between @dstart and @dend) AS delegation_cost
FROM employees

join (select  @dstart := str_to_date('10/01/2017','%m/%d/%Y'),
              @dend   := str_to_date('11/30/2017','%m/%d/%Y')) d

order by 1;

I am wondering if it is possible to do some basic summing for instance (to_hand_cost + delegation_cost) as simple_sum I assume it might be not possible but repeating the selects again to get the values seems overwhelming. Got any suggestions?

Mysql playground mcve : https://www.db-fiddle.com/f/5FtV24czfAfUiE1dj5EeYt/13


texts: How to calculate conditional running additions in SQL Server 2012

texts:
I have a table with below mentioned structure

create table #test
(
ID int identity(1,1),
A int,
B int
);

and having data as

ID  A   B
1   9   NULL
2   9   1
3   9   2
4   8   NULL
5   8   4
6   8   5

I want to calculate a new column let us say C based on values of this table.
If we consider it as an excel table then, formula for calculation would be =IF(B1="",C1+A2,B1+A2).
i.e. first value of calculated column would  always be A1 and then from second row I have to apply above excel formula in SQL SERVER 2012 to calculate a new column.

I am aware about LAG and LEAD functions, but my computation involves value of computed column of previous row for computation of current computing value.

I am clueless about such calculation in SQL SERVER.
What would be the best way to compute such a condition?

EDIT
Expected output would be

9
18
10
10
18
12

The select statement which I tried is

SELECT
    CASE WHEN LAG(B) OVER (order by ID) is null THEN 351
    ELSE LAG(B)
    OVER     (order by ID) + A END
FROM #test;

EDIT 2
Test Data:

INSERT INTO #test (A, B)
VALUES
    (NULL,355)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)

Result:

0
355
355
355
355
355


texts: SARG cardinality estimate, why not full-scan?

texts:
Why there is no full-scan (On SQL 2008 R2 and 2012)?

Test data:

DROP TABLE dbo.TestTable
GO
CREATE TABLE dbo.TestTable
(
   TestTableID INT IDENTITY PRIMARY KEY,
   VeryRandomText VarChar(50),
   VeryRandomText2 VarChar(50)
)
Go
Set NoCount ON
Declare @i int
Set @i = 0
While @i < 10000
Begin
   Insert Into dbo.TestTable(VeryRandomText, VeryRandomText2)
      Values(Cast(Rand()*10000000 as VarChar(50)), Cast(Rand()*10000000 as VarChar(50)));
   Set @i = @i + 1;
End
Go
CREATE Index IX_VeryRandomText On dbo.TestTable
(
    VeryRandomText
)
Go

When execute query:

Select * From dbo.TestTable Where VeryRandomText = N'111' -- bad

Get warning (as expected, because comparing nchar data to varchar column):

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[DemoDatabase].[dbo].[TestTable].[VeryRandomText],0)" />

But then i see execution plan, and i can see, that it is not using full-scan as i would expect, but index seek instead.

Of course, this is kind of good, because in this particular case execution is way faster than if there would be full scan.

But i can not understand how SQL server came to decision to make this plan.

Also- if the server collation would be Windows collations on server level and SQL Server collation database level, then it would cause full scan on the same query.


texts: Plaintiff and defendant have the same attributes

texts:
I am working on my final year project, "civil case management system" related to civil law.

I have a problem to store attributes for both plaintiff and defendant. They have the same attributes. I am not sure what is the best solution.


texts: Postgres: Performance Issue: Query on enormous data fails to use index

texts:
This is the schema of task_statuses table
                         Table "public.task_statuses"
     Column     |            Type             | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
 id             | uuid                        |           | not null |
 updated_at     | timestamp without time zone |           | not null |
 status         | task_status                 |           | not null |
 status_details | json                        |           |          |
Indexes:
    "task_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_task_statuses_id_updated_at" btree (id, updated_at)
Foreign-key constraints:
    "task_statuses_id_fkey" FOREIGN KEY (id) REFERENCES tasks(id) ON DELETE CASCADE

The table however is huge and has 150GB of data in production.
I am trying to run an extremely simple query
 SELECT
            ts.id
        FROM task_statuses ts
        WHERE
            ts.status IN ('succeeded', 'failed', 'cancelled')
        ORDER BY ts.id, ts.updated_at desc LIMIT 1000

It keeps timing out in production. When I remove ORDER BY the query runs successfully. Since, I have index in id and udpated_at, I am not sure why order by is timing out.
explain analyse times out as well.
Here is the explain for the above query.
Limit  (cost=10651159.84..10651276.51 rows=1000 width=24)
  ->  Gather Merge  (cost=10651159.84..10744721.60 rows=801902 width=24)
        Workers Planned: 2
        ->  Sort  (cost=10650159.81..10651162.19 rows=400951 width=24)
              Sort Key: id, updated_at DESC
              ->  Parallel Seq Scan on task_statuses ts  (cost=0.00..10628176.10 rows=400951 width=24)
                    Filter: (status = ANY ('{succeeded,failed,cancelled}'::task_status[]))

Query plan without order by:

https://explain.depesz.com/s/CfIU

Helpful links:

https://explain.depesz.com/s/G3VB

https://explain.depesz.com/s/hJCL

Suggestions or help would be much appreciated.


texts: At what point is SQL Server's is_policy_checked enforced?

texts: At what point is SQL Server's is_policy_checked enforced - just at next password change or at each login?


texts: Generate unique id based on criteria from multiple rows

texts:
I've got the following scenario, I've got a table like this

| pid | itemId |
|-----|--------|
|123  | item1  |
|123  | item2  |
|234  | item1  |
|234  | item3  |
|456  | item1  |
|456  | item2  |
|567  | item1  |
|567  | item2  |
|567  | item3  |

I need to get a unique integer id based on the unique combination of items. So if two persons have chosen the same items, the unique combination should be the same number.

For example:

| pid | itemId | comb |
|-----|--------|------|
|123  | item1  |  1   |
|123  | item2  |      |
|234  | item1  |  2   |
|234  | item3  |      |
|456  | item1  |  1   |
|456  | item2  |      |
|567  | item1  |  3   |
|567  | item2  |      |
|567  | item3  |      |

The number of possible combinations at the moment are around 300. So when filtering it might be easier to filter by just one number in one column than filtering with multiple criteria.


texts:
I am trying to run this t-sql query but I keep getting an error of

  Incorrect syntax near the keyword 'END'

How should this be re-written in order to execute properly?  What I want to achieve is if my variable @d2 >= GetDate() I want to run a check to see if @d2 exists in my table farquard if it does then back-up @d1 by 1 day, if it does not then back-up @d1 by two days.

This is my with errors syntax, can someone help me?

Declare @d1 datetime, @d2 datetime
Set @d1 = '01/01/2013'
Set @d2 = '01/04/2013'

if @d2 >= CAST(GetDate() As Date)
Begin
  if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
      BEGIN
        Set @d2 = CAST(GetDate()-2 As Date)
    END
ELSE
      BEGIN
        Set @d2 = CAST(GETDATE()-1 As Date
      END
  END
End


texts: Run Querys on List of Databases (For Each loop?)

texts:
I have a selection of about 15 databases on an SQL server that I want to run some query's on. I do not want to affect any of the roughly 10 other databases.

Specifically, I want to run the following commands:

USE MASTER;
GO

ALTER DATABASE DATABASENAME
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE DATABASENAME
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO

EXEC MASTER.dbo.sp_detach_db @dbname = N'DATABASENAME'
GO

My goal is to take these databases offline, detach them, move the .mdf and .ldf files to a different disk, then re-attach them using the GUI.

In an ideal world, I would also love to be able to then run the following command on them after all have been re-attached:

ALTER DATABASE databasename SET ENABLE_BROKER

In a perfect world, I would be able to designate a list of databases and then loop through all of the commands.

Something PowerShell-y would look like this:

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
USE MASTER;
GO

-- Take database in single user mode
ALTER DATABASE $Databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Take databace offline
ALTER DATABASE $Databasename
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO

-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'$Databasename'
GO
}

And then after something like

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
ALTER DATABASE $Databasename SET ENABLE_BROKER
}

I tried this T-SQL ‘while loop approxmiation’ without any luck:

--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO

--Loop through DB names, taking them offline and detaching them.
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 ,
        @DBName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO
-- Take database in single user mode
ALTER DATABASE @DBName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Take DB Offline
ALTER DATABASE @DBName SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname
GO

   SET @LoopCounter  = @LoopCounter  + 1
END

UPDATE 1
Here is my revised script

--Declare base Dynamic SQL Variables

DECLARE @sql nvarchar(max);

DECLARE @sql2 nvarchar(max);

--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO

--Loop through DB names, taking them offline and detaching them.
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 ,
        @DBName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO

 SET @sql = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';

 SET @sql2 = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;';

EXEC sys.sp_executesql @sql;

EXEC sys.sp_executesql @sql2;

EXEC MASTER.dbo.sp_detach_db @dbname;
GO

SET @LoopCounter  = @LoopCounter  + 1

END


texts:
Not too long ago I did a bulk operation that I ended up needing to rollback. My transaction log file for that hour was 7 GB. I found that my log file is now over half the size of my data file and I'm assuming that transaction caused that growth since nothing changed with the backup schedule (the log backup has always succeeded and the full has intermittently failed recently due to space).

Both this time and in the future, what operations should I follow up with after doing large maintenance (like index rebuilds, full table updates, etc)?

I'm realizing I may need to shrink my log file back to a more typical size (since my maintenance activity was not a normal operation), but I also hesitate about this - if I have the space (I'm about to make space), should I just leave the transaction file at that size since it did need that space at one point, and will probably again (even if not during normal transactions)?

Are there any other operations I should consider during maintenance/release windows (reduced or full shutdowns)?

For reference, I have in mind our production SQL Server 2016 server with a 2008 compatibility database; currently 30 GB in size in Full recovery mode; full backups are nightly and transaction logs are hourly.


texts: Causes for pg_class locks except DROP TABLE

texts:
I do experience the following situation on a PostgreSQL 11:

A transaction with unknown statements creates an ACCESS SHARE lock on pg_class.
Another connection wants to execute DROP TABLE which causes an ACCESS EXCLUSIVE lock on pg_class. This operation is blocked because operation #1 still holds the lock.
Following that any new connection to the DB is waiting forever, because it needs to access pg_class as well.

The only way to proceed is to either kill operation #1 or #2.

While I can reproduce the situation with manual locking the tables, I do not find an explanation what causes the pg_class lock on operation #1. That is, pg_stats_activity only shows a SELECT as current query which is idle in transaction. I suspect this not being the statement creating the lock, however.

Thus, any input on what could cause ACCESS SHARE locks on pg_class except DROP TABLE is highly appreciated.

Update atm it is also unclear why DROP TABLE wants to lock pg_class.


texts: Why does dropping a MVIEW trigger a full refresh?

texts:
I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again.

The MVIEW was created with a statement like this:

create materialized view foobar
  refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;

The creation took some time.

Now after realizing it was the wrong script I ran:

drop materialized view foobar;

Which was running for some time as well. I first thought it might be due to some locks or other sessions blocking this, but no: when I queried v$session I was seeing that my drop statement actually triggered a full refresh of the mview:

USERNAME  | SID  | SERIAL# | PROGRAM                  | STATUS | SQL_ID        | SQL_TEXT
----------+------+---------+--------------------------+--------+---------------+-------------------------------------------------------------------------
MY_USER   |  294 |   29131 | oracle@hostname (J002)   | ACTIVE | 6snjgrzbtynyc | INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MY_USER"."FOOBAR" select .....

And indeed the drop materialized view ran for about the same time as the create.

So my question: why would Oracle trigger a full refresh when I drop a materialized view?

Environment: Oracle 11.2.0.3 64bit

P.S.: I think the alias indexed-view for materialized-view is wrong. Although both reference the same underlying concept they are distinct names used by Microsoft and Oracle. Someone interested in materialized views in Oracle isn't probably interested in indexed views in SQL Server. But I don't know how this alias can be removed


texts: Cannot grant execute to stored procedure

texts:
When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below:

Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

This is happening in any procedure where I try to GRANT EXECUTE permissions; I have tried to grant to other users as well, but same error.

ALTER PROCEDURE TEST
AS
BEGIN
   SELECT 1
END


texts: Transfer database backup from Production to Staging during business hours

texts:
We have a nightly Production to Staging database sync that failed this morning. I need to copy a backup from 1 Production server to 1 Staging server and I'm not sure of the best way to do it without interrupting services or affect performance.

The backup is 15GB and the servers have a high speed connection between them. Would it be best to simply copy the backup from Production to Staging and perform the restore manually, or would this affect performance too much?


texts: Get data from previous years dynamically

texts:
I have a table with columns id, fname, date_field. date_field's records are:

2011-03-01
2012-03-01
2013-03-01
2014-03-01
2015-03-01
2016-03-01

I want the data for the past 2 years for each record from that date which the table has.

For example:

2012 year having only one data before that is 2011
2013 year data having two data before 2 year data which are 2012 and 2011
2014 year data having two data before 2 year data which are 2013 and 2012
2015 year data having two data before 2 year data which are 2014 and 2013
2016 year data having two data before 2 year data which are 2015 and 2014

Note: Need to work dynamically for whatever data is in table, not by entering the date manually.

I want the query for MySQL.


texts:
I'm looking for a way to retrieve search results from an sqlite3 db, including some text around the query, to show context in the search results.

I'm not finding any examples of this. Is this possible with pure SQL? Or am I better off (or stuck) using server code to do this after pulling the field from the db?

Just a little example, let's say a field will contain the string "the quick brown fox jumped over the lazy dog". If I search for "fox", I only want the bolded part to be retrieved.

And if possible, do this for every instance of "fox" found in the entire text, not just the first one


texts: Stuck with self-referencing linked server - will replacing the primary server name be better?

texts:
A vendor app uses 4-part names to reference objects.  I am pushing back on that, but the current version is what it is.  We installed the app using a SQL Alias for all the good reasons you do that, but the app had tons of errors because its four part references were starting with the alias instead of the local server name.  Defining the alias on the db server had no effect - the references were being made within the DBEngine.

To get around the errors, I added a second server definition (linked server) named after the alias that referred back to the same DBEngine.  I recently heard about the performance costs of linked servers (e.g. no caching) and about how bad an idea it is to define a linked server that refers to the local server.

I am stuck with this version of the app for now, so I cannot do anything about the four-part references and cannot reinstall without the alias (my client is not willing to pay for that work).

Would it help to delete the default server definition and replace it with the alias name instead of the machine name.

Comments? Warnings? Risks? Will doing that avoid the performance issues associated with self-referencing linked servers?

As a secondary question, I would be interested in a clear explanation of why four part names are a bad idea - something I can pass on to the vendor.


texts: Weird WHERE Clause Behavior. Why does this return a row?

texts:
In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example?

WITH SRC AS (SELECT cast('12345' as varchar) DEMO)
SELECT * FROM SRC WHERE DEMO='12345  '

What if one needs to query to find '1234 ' but not '1234'


texts: Should I create a multi-column UNIQUE index?

texts:
I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits):

CREATE TABLE `Visits` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `User` int(10) unsigned NOT NULL,
  `EventKind` enum('search','purchase','subsectionList','event','like','superprice','toursList','abonementsList','abonement') NOT NULL,
  `SiteSection` enum('section','subsection','superprice','region','city','children','tour','abonement') NOT NULL,
  `Object` int(10) unsigned NOT NULL DEFAULT '0',
  `Count` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `User` (`User`),
  KEY `EventKind` (`EventKind`),
  KEY `SiteSection` (`SiteSection`),
  KEY `Object` (`Object`)
) ENGINE=MyISAM;

The quadruple User/EventKind/SiteSection/Object is always unique. My question: Is it worth (especially due performance considerations) to add a 4-columns UNIQUE key?

This data will be used to show a user these sections of the site which are related with his previous visits.


texts: Logon failed for login due to trigger execution / LOGON TRIGGER

texts:
I have this situation.
I created a logon script to block any session for specific logins. These logins are in a table.
So the behaviour of my script is simple.
Get the login from the table, compare it with the login who wants to access and then insert some information to another table.
The question here is:

I tested in one instance and everything went fine but in other instance and now in the previous instance when I enable the logon trigger appears a pop up and block my personal account.
My account is not included in the table so should not have this behaviour.
Obviously the part to insert date in a table, doesn't work.
Also other logins have been blocked

CREATE TRIGGER Accounts_V1
ON ALL SERVER WITH EXECUTE AS 'DBA'
 FOR LOGON
AS
BEGIN

DECLARE @Logon as varchar(50)
DECLARE @AppName as varchar(250)
DECLARE @ComputerName as varchar(50)
DECLARE @ServerName as varchar(50)
DECLARE @Original_Login as varchar(50)

SET @ComputerName =  HOST_NAME ()
SET @ServerName  =  @@servername
SET @AppName =  APP_NAME()
SET @Original_Login = ORIGINAL_LOGIN ()
SET @Logon = ( select LoginName FROM [dba]..[MissusesAccounts_test]

where LoginName NOT like '%WIN%' and ( @AppName like '%Microsoft%' or @AppName like '%PowerShell%' ) and @ComputerName not like '%sql%')

if (@Logon = @Original_Login)

    INSERT INTO [dba]..[LogonAccounts_test]
           ([ComputerName]
           ,[ServerName]
           ,[AppName]
           ,[Logon])
     VALUES
           (@ComputerName
           ,@ServerName
           ,@AppName
           ,@Logon)

        BEGIN
            print 'This login:  '+@Logon+' should not be used by individuals to run interactive queries in '
            +@AppName+' , and email has been sent to the Development Manager to investigate'

        END

END
REVERT


texts: Accessing unsharded database in a MongoDB shard cluster without going through mongos?

texts:
As far as I can understand, not all databases in a MongoDB shard cluster have to be sharded; I can keep some databases unsharded, on only one of the shards.

So suppose I have a shard cluster, with shards (i.e. replica sets) rs0 and rs1. rs0 has a database foo, which is unsharded. I have no plan to shard it or move it to rs1.

Is there any harm in letting some clients connect to rs0 directly and use the foo database without going through mongos?

I have only tested that it is possible to connect directly to rs0 and make queries in foo, but I don't know if it is potentially dangerous in my use case.


texts: How to get the current row in postgres function?

texts:
I have a table where I have a column called event varchar(255) and user. An event column is a set of strings which tells me in what state was the user when the event was received.

I want to count how many events of each type I have per user.

So I'd like to create a function which would receive as a parameter an event value and then go over each row in the table and if the current row event equals the parameter it will return 1 else 0. The problem is don't how to get the reference to the current row in a postgres function. This is my code so far:

CREATE OR REPLACE FUNCTION decode_event (eventVar varchar(255))
RETURNS integer AS $result$
  BEGIN
    if event like eventVar
      then return 1;
    else
      return 0;
    END IF;
  END;
$result$ LANGUAGE plpgsql;

Then I would run a query like the this:

  SELECT
  user,
  SUM(decode_event('start')) AS start,
  SUM(decode_event('end')) AS end
  FROM stats
  GROUP BY user;


texts:
I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist.

declare
 tableExists number;
begin

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE';

if tableExists>0 then
    execute immediate 'drop  table MY_TEST_TABLE';
end if;

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE2';

if tableExists>0 then
execute immediate 'drop table MY_TEST_TABLE2';
end if;

--Here I create both tables: MY_TEST_TABLE and MY_TEST_TABLE2

My question is:
Is there a way to avoid repeating the code for checking if the table exists? Is there a way that I could call that code as a method passing the table name as parameter?

I believe I could create/declare a function and then use that. But that kind of function is not temporal. I know this is not like C or C++. But I was wondering if there was a way I could have a sort of "private method" for this script to test the existence of my tables and that way I wouldn't have to repeat the code, but the method wouldn't remain after my script was finished.

What is the correct way, if there is any, to achieve what I want?


texts: many Servers of SQL server 2012 needs mirroring

texts:
I have now simply 2 SQL Server 2012.
I want to configure database mirroring between the 2 servers.

My questions are:

How can it automatically change host of one if the other one was dropped ?
Is the performance of mirroring acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?


texts: How to download or place a copy of original tables with locked-permissions via remote connection in Oracle SQL Developer?

texts:
N00b question here: I'm still learning Oracle SQL Developer.  Although I understand how to query databases/tables remotely, I'm unable to manipulate the data due to permissions-basically I would like to download/export copies of the databases/tables so I can manipulate or adjust the data within the table.

I'm running Oracle SQL Developer on Mac OS X Yosemite and I'm unable to place a copy of the original table in my table space.  I've consulted Oracle dox, but I believe these are dated because the GUI isn't as described.  I'd appreciate any assistance.  Thank you.


texts: How to monitor history of database usage if login auditing is not enabled?

texts:
I have a number of databases on multiple servers that don't seem to be used any longer.  I'd like to drop these databases, but I'm uncertain if they are actually in use.

I can't find their owners, there are no processes linked to them, and nothing has been written to the tables for a few months now.

Login auditing was not enabled prior to today.

Servers versions range from 2005 to 2014, but most of these are on 2008 R2.

Is there a way to check the history of logins for these databases, going back 6 months, just to double check with these users, before taking the databases offline?

I got a suggestion to look at index usage history; is someone able to share a script for that?


texts: What does reslen and other means in mongodb log?

texts:
database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

it seems that ntoreturn means number to return. ntoskip is numbers to skip. nscanned seem to be the sum of the 2.

What is reslen?

I search google with no answer. What site explain what reslen is?


texts:
I have a query which uses listagg it is as follows

SELECT listagg(path1.PATHWAY_ID,'|like||id|')within GROUP (
ORDER BY path1.PATHWAY_ID),
  listagg(path1.PATHWAY_NAME,'|like||name|') within GROUP (
ORDER BY path1.PATHWAY_ID)
FROM CI_PUBLIC.GG_PATHWAY path1,
  ci_public.gg_pathway_folder_tree path2
WHERE path1.PATHWAY_ID=path2.DESCENDANT_ID
AND path2.ANCESTOR_TREE_CODE='**VALUE TO BE PASSED**'
AND path2.ANCESTOR_DEPTH=path2.DESCENDANT_DEPTH-1;

The number of results the query should return is 131 but I get below error

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

But when I pass a different value to path2.ANCESTOR_TREE_CODE it retrieves results.
There is nothing wrong with the query the only problem is when it has more results it gives error.

Is there anyway that I can get more results using listagg?


texts:
I have follow code and after I ran them in SQL Server Management Studio, I expect to get same results because those 3 parts created exact same SQL Statement. Part 3 SQL statement is created by part 1.
Part 1: run SQL statement using EXEC sp_executesql passing parameters.
Part 2 and 3 just run Select statement
I got same records, however results show different sorting sequence for column FSONUM, anyone knows why?
Any suggestions/helps would be greatly appreciated.
Thanks

Code:

declare @startDate datetime = cast('2019-07-17' as datetime),

    @endDate datetime =cast('2019-07-17' as datetime),

    @sortQualifier varchar(20)='date',

    @sortOrder varchar(20) ='D',

    @sort varchar(100),

    @SQLString nvarchar(max) ,

    @ParamDefinition nvarchar(500) = N'@startDate datetime, @endDate datetime'

IF @sortOrder <> '' SET @sort = ' DESC'

IF @sortQualifier = '' SET @sort = ''

SET @SQLString = N'SELECT manifestDate, FSONUM, FPIECES,FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate >= @startDate AND manifestDate <= @endDate ' +

        CASE @sortQualifier

            WHEN 'date' THEN N'ORDER BY manifestDate'

            WHEN 'tracking' THEN N'ORDER BY FSONUM'

            WHEN 'service' THEN N'ORDER BY FSERVICE'

            WHEN 'pieces' THEN N'ORDER BY FPIECES'

            WHEN 'weight' THEN N'ORDER BY FWEIGHT'

            WHEN 'consignee' THEN N'ORDER BY FSCOMPANY'

            WHEN 'city' THEN N'ORDER BY FSCITY'

            WHEN 'state' THEN N'ORDER BY FSPROV'

            WHEN 'zip' THEN N'ORDER BY FSPOSTAL'

            WHEN 'customer' THEN N'ORDER BY FREFERENCE'

            WHEN 'part' THEN N'ORDER BY FINSTRUCT'

        END + @sort

EXEC sp_executesql @SQLString, @ParamDefinition, @startDate, @endDate

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT
FROM psorderh WHERE manifestDate >= @StartDate AND manifestDate <= @EndDate ORDER BY

    CASE @sortQualifier

        WHEN 'date' THEN manifestDate

        WHEN 'tracking' THEN FSONUM

        WHEN 'service' THEN FSERVICE

        WHEN 'pieces' THEN FPIECES

        WHEN 'weight' THEN FWEIGHT

        WHEN 'consignee' THEN FSCOMPANY

        WHEN 'city' THEN FSCITY

        WHEN 'state' THEN FSPROV

        WHEN 'zip' THEN FSPOSTAL

        WHEN 'customer' THEN FREFERENCE

        WHEN 'part' THEN FINSTRUCT

        END  DESC

    END

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate >= @StartDate AND manifestDate <= @EndDate ORDER BY manifestDate DESC


texts:
I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables:

CREATE TABLE IF NOT EXISTS `mo_sms` (
  `id_MO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `precio` float(11,2) unsigned DEFAULT '0.00' COMMENT 'Precio del MO',
  `id_user` int(10) unsigned NOT NULL,
  `state` char(1) NOT NULL DEFAULT '0' COMMENT '0=Por procesar 1=Procesado',
  `tipo_sms` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MO`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_sms_proceso_state` (`sms_proceso`,`state`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`),
  KEY `ix_varios` (`anio_sms`,`mes_sms`,`comando_id`,`shortcode_id`,`brand_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MO' AUTO_INCREMENT=82972 ;

CREATE TABLE IF NOT EXISTS `mt_sms` (
  `id_MT` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL DEFAULT 'c' COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `tipo_sms` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `id_MO` int(11) unsigned NOT NULL,
  `state` char(1) DEFAULT '0' COMMENT '0=Por Procesar 1=Procesado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MT`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MT' AUTO_INCREMENT=93853;

I have some values in DB2 that should be migrated (inserted) in DB1. My problem here is I don't know how to get the records from DB2.mo_sms tables and those relations from DB2.mt_sms and then insert to DB1.mo_sms and DB2.mt_sms. For example I'll get all the records from mo_sms with fecha_sms LIKE '%2013-04-19%' then if I insert those records in DB1.mo_sms new IDs will be generated then the integrity will be lost. Should I make this programatically or there is any way to do this using just SQL queries? I'm using MySQL as DBMS.


texts: MySQL eats up all the memory from the RHEL OS

texts:
Currently, I have MySQL server installed in the AWS RHEL EC2 instance.

The team was running heavy data processing in the DB and we found out that every now and then the DB restarted itself. After troubleshooting, we realized that the DB was actually killed by the OS! Because the MySQL is so hungry that it consumes all the memory in the OS.

The OS has 124GB RAM and 68GB is allocated to innodb_buffer_pool_size.
Attached my.cnf file

Question:

How do we prevent the OS from killing the mysqld?

    [mysqld]
# Server configuration
user=mysql
port=3306
socket=/home/mysql/server/run/mysql.sock

# File locations
datadir=/mysql/data
basedir=/home/mysql/server
lc_messages_dir=/usr/share/mysql-8.0
plugin-dir=/lib64/mysql/plugin
innodb_log_group_home_dir=/redolog/redologfiles
innodb_data_file_path=ibdata1:12M:autoextend
log-error=/home/mysql/server/logs/mysqld.log
innodb_log_file_size=2147483648
innodb_log_files_in_group=8
innodb_page_size=16384
innodb_checksum_algorithm=crc32
innodb_buffer_pool_filename=ib_buffer_pool
innodb_undo_tablespaces=2
innodb_buffer_pool_filename=ib_buffer_pool
skip-log-bin

# New server config
character-set-server=latin1
collation-server=latin1_swedish_ci
connect_timeout=31530000
net_read_timeout=31530000
net_write_timeout=31530000
innodb_buffer_pool_size=68719476736
innodb_buffer_pool_instances=64
innodb_temp_data_file_path=ibtmp1:512M:autoextend:max:512000M
innodb_max_undo_log_size=4294967296
innodb_read_io_threads=16
binlog_expire_logs_seconds=259200
#expire_logs_days=10
max_binlog_size=1073741824
sql_mode = ''
max_connections=300
log_timestamps=SYSTEM
local_infile = 1
max_digest_length = 3072
performance_schema_max_digest_length=10240
max_digest_length=10240
performance_schema_max_sql_text_length=10240
innodb_adaptive_hash_index = 0

interactive_timeout  = 86400
mysqlx_interactive_timeout  = 86400
mysqlx_wait_timeout  = 86400
wait_timeout  = 86400

general_log = OFF
general_log_file = /home/mysql/server/logs/mysqld_db.log
[client]
port=3306
socket=/home/mysql/server/run/mysql.sock

#slow_query_log_file=/home/mysql/server/logs/mysql-slow.log
#slow_query_log=ON
#general_log_file=/home/mysql/server/logs/mysql_gen_db.log
#general_log=ON


texts: How to handle a table schema with a new relationship subtype in an interviews scenario?

texts:
I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below

But now we have added a new type of interview which would require different types of reviews based in the interview type. I have added a many to many (M:N) relationship to store the interview type, but I am stumped as to the best way to go about adding multiple review types...

One solution I thought of was to add another table for reviews with the many to one (M:1) relationship which would contain all the data for the new review type. This would mean that the application would have to conditionally look for the review based on the type of interview, and it would lead to blank columns for all the unused review types...

How would you go about structuring this?


texts: sp_whoisactive @get_locks parameter xml error

texts:
I often receive this error when running the following command

sp_whoisactive @get_locks = 1

  Msg 6841, Level 16, State 1, Procedure sp_WhoIsActive, Line 4287 FOR XML could not serialize the data for node 'Lock/@resource_description' because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive. Warning: Null value is eliminated by an aggregate or other SET operation.'

I believe it is related to the grabbing the xml of the locks being held, however I notice it happens frequently when there is blocking occurring. Is there any way around this or is it a known issue? Running SQL Server 2012, and latest version v11.32 of sp_whoisactive.

Edit

Following the suggested option, I’m having trouble locating this data. I mostly encounter the message when there is blocking on the server, once I encounter the message I take the parameter off and try to see which object it may be coming from, but it’s difficult because there are many threads running. Is there any easier way of going about it? I initially didn’t think it was data but an object name, because when I see the locks xml it normally only contains object names, I didn’t realize actual data is being put in there that may be resulting in this message?

Edit 2

Following the advice from Erik, I was able to track down the object with the SOH character outputting the results of the #locks table. However ultimately what I found most useful is using the work-around of get_task_info = 2 and get_additional_info = 1, when running these parameters I do not receive the error message and still receive the information I need. I am still going to explore trying to add that character into the replace statements in sp_whoisactive so I can still use the get_locks parameter in the case I need to see the full XML.