texts: Best Practice For Data Views with Linked Server in SQL Server

texts:
In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter.

The problem is that I have to combine the information from multiple tables, and some of them are on another server.

Currently, I have a view with most of the information, composed by multiple joins of several tables on a server (A), and another view in another server (B) that joins that view  on server A with several other tables from the same server B.

So the flux of data as I understand it goes like this:
Final View on Server B = Server A View JOIN Server B Tables

Here is the template for the Server A view:

ALTER VIEW [dbo].[DataView_ServerA]
AS
SELECT
    ss.Field,
    ss.Field,
    ss.Field,
    ss.Field,
    (CASE   WHEN ss.Field is null then 'Field not Assigned' else ss.Field end) as Field,
    mfl.Field as Field,
    St.Field,
    (CASE   WHEN ss.Field=0 THEN CAST(0 AS BIT)
            WHEN ss.Field=1 THEN CAST(1 AS BIT)
            else ss.Field end) as Field,

    fh.Field AS Field,
    ssABC.Field AS Field,
    (CASE WHEN ssABCAB.Field IS NULL OR ssABC.Field IS NOT NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END) AS Field,
    (select (CASE WHEN count(Field)>=1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) as treatedByAB from table where Field='Example' and id in (select Field from [table] where Field= fh.Field)) as Field

FROM
    dbo.table AS ss WITH (nolock)
LEFT OUTER JOIN
    dbo.table as St  WITH (nolock) on ss.Field!=St.Field
LEFT OUTER JOIN
    dbo.table AS ssABC WITH (nolock) ON ss.Field = ssABC.Field AND ssABC.Field = St.Field
LEFT OUTER JOIN
    dbo.table AS fh WITH (nolock) ON fh.Field= ssABC.Field
LEFT OUTER JOIN
    dbo.table AS ssABCAB WITH (nolock) ON ss.Field= ssABCAB.FieldAND ssABCAB.Station = St.Station
LEFT OUTER JOIN
    dbo.table mfl ON ss.Field=mfl.Field and ss.Field=mfl.Fieldand ss.Field=mfl.Field and mfL.Field='Example'

And the template for the Final View on Server B is:

ALTER view [dbo].[FinalDataView_ServerB] as
select ABCraw.*,
Tch.Field,
cat.Field,
f.Field

FROM [LinkedServer].[Database].[dbo].DataView_ServerA as raw

LEFT OUTER JOIN
    dbo.table AS f ON f.Field=raw.Field
LEFT OUTER JOIN
    dbo.table AS act ON f.Field = act.Field
LEFT OUTER JOIN
    dbo.table  AS cat ON f.Field = cat.Field
LEFT OUTER JOIN
    dbo.table AS Tch ON f.Field= Tch.Field

But the time that it takes to retrieve registers is normally above 7 minutes, with no distinction to the quantity.

I you could provide me of some advice of how to rewrite the query in order to optimize it or configure the servers to load the data more quickly, I would appreciate it a lot.

Thanks in advance.


texts: Do you set an index on JOIN clauses or where clauses, or both?

texts:
Say my query looks like:

SELECT t1, t2
FROM t1
  LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid)
WHERE t1.enabled = 1 AND
      t1.startDate <= ??? AND
      (t1.counter = -1 OR
       t2.counter IS NULL OR
       (t1.counter > t2.counter)

Now this table might have a few hundred thousand rows in it.

Would you suggest I put an index on the JOIN clause only like this?

  t2.id t2.userid

What about the where clause?  Or is the join clause more important?

I realize testing is important, but in theory what should be done?

(This is for SQL Server 2000)


texts:
I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do this with myisamchk.

At the MySQL documentation I read this:

  To coalesce fragmented rows and eliminate wasted space that results
  from deleting or updating rows, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

  You can optimize a table in the same way by using the OPTIMIZE TABLE
  SQL statement. OPTIMIZE TABLE does a table repair and a key
  analysis, and also sorts the index tree so that key lookups are
  faster. There is also no possibility of unwanted interaction between a
  utility and the server, because the server does all the work when you
  use OPTIMIZE TABLE.

Does this mean that myisamchk -r -a -S does the same thing as OPTIMIZE TABLE?

If they do do the same thing, why does myisamchk work but OPTIMIZE
TABLE run out of space?


texts: Group concat all child ids against parent row using query in Mysql


texts: Long Sync IO: Scheduler X had 1 Sync IOs in nonpreemptive mode longer than 1000 ms

texts:
in SQL 2014 SP3 i got quite a few "Long Sync IO: Scheduler x had 1 Sync IOs in nonpreemptive mode longer than 1000 ms". what is the size that SQL reports for the sync IO, is it 8K or varies based on activity: data read/transaction write/or lazy writes or checkpoint activities?
Thank you,


texts: User cannot create schema in PostgresSQL database

texts:
I'm trying to setup a "deployment" user which can create and alter tables on an existing database in addition to selecting, updating, inserting and deleting records.

Here is what I've tried so far:

-- Create deployment user
CREATE ROLE deploy_user WITH LOGIN PASSWORD 'deploy_user';

-- Grant connect and create
GRANT CONNECT, CREATE ON DATABASE my_database TO deploy_user;

-- Grant create schema privilege
ALTER ROLE deploy_user CREATEDB;

-- Change db owner to deployment user
ALTER DATABASE my_database OWNER TO deploy_user;

-- Grant CRUD operations
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO deploy_user;

None of the above grants work. What I end up with is a user which can login but that's it. I cannot select, insert, update, delete on any tables. I cannot make changes to the schema either.

Can anybody help out?


texts:
Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql.

I need to do the following:

Query the information schema to get the parameter names to an sp
SELECT  PARAMETER_NAME, DATA_TYPE from information_schema.parameters where   specific_name=@SPName
Create a dynamic query using this names like select @param1+","+@param2 in a function.Which accepts @SPName as the parameter
call this function in my sps to get the dynamic sql
Execute this sql in my procedure and get the param 1 and param 2 value (param 1 +param2)
Update this value in my spcallTracking Table SpCallDetails field

I have tried this method ,but getting error like
Must declare the scalar variable (param 1)
and the reason i Guess is Dynamic sql runs in a different session and therefore variables defined outside the dynamic query will not be available to the dynamic query

https://stackoverflow.com/questions/14960777/must-declare-the-scalar-variable-error-sql

1. Table-

CREATE TABLE [dbo].[Single](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Age] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
    [ID] ASC,
    [Name] ASC,
    [Age] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

2. Function-

CREATE FUNCTION [dbo].[udf_getSPName]
(
    @SPName nvarchar(100)
)
RETURNS  nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)='SELECT '
DECLARE @tmpParameterDetails table (ParamName varchar(200),DataType varchar(200))
INSERT @tmpParameterDetails
    SELECT  PARAMETER_NAME, DATA_TYPE from information_schema.parameters
    where specific_name=@SPName
    SET @ReturnString=@ReturnString+
                (SELECT STUFF(
                    (SELECT ','+ParamName
                     FROM
                            @tmpParameterDetails

                    FOR XML PATH (''))
                 ,1, 1, ''))

     Return @ReturnString

  RETURN @ReturnString
END

3. Procedure-

CREATE PROCEDURE [dbo].[usp_Single_insert]
    @Name nvarchar(50),
    @Age  int

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

   DECLARE @ReturnString as nvarchar(max)

    INSERT INTO [Single]
           ([ID]
           ,[Name]
           ,[Age])
     VALUES
           (NEWID(),
            @Name,
            @Age)
            DECLARE  @CreatedBy as nvarchar(100)='test'
            DECLARE  @CreatedOn as datetime=getdate()
            DECLARE @ColV nvarchar(max)

           DECLARE @ParmDefinition nvarchar(500);

    SET @ReturnString=[dbo].[udf_getSPName]('usp_Single_insert')
    EXECUTE sp_executesql @ReturnString

END


texts: Schema design for privacy settings in MongoDB

texts:
I have a set of documents that can have various privacy settings around them:

They can be completed public (any registered user) can view them
They can be viewable only by people following you (this
"Followers" array is stored in each User's document)
They can be private to the person who published the document as well.
They can have custom privacy which allows you to name individual users who can view the document. In addition, you can allow groups of users to also view the document (e.g. there could be a group called "Sample Group" which has 20 users in it. You could allow said group to view the tile.)

I am lost as to how to implement a schema for this efficiently in MongoDB and would love some insight into best practices for implementing this schema.


texts: How to achieve many to many relationship in this database design?

texts:
I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist.

What I have tried so far is:

 user (user_id(pk), user_name)

 trip(trip_id(pk), trip_name, user_id(fk))

 wishlist(trip_id(fk), user_id(fk))

But, since  multiple users can add multiple trips into their wishlist, How to associate these relations?

And if user retrieves his personal wishlist, the associated trips in the wishlist for 'that' particular user can be shown?


texts: Easy way to have non-tech users modify database?

texts:
We're in the process of engineering a BI solution.

One problem we have is that sadly a lot of HR information (start date, department, department change date, work hours, etc etc) -- seems to me maintained manually in an Excel spreadsheet. Needless to say it lacks standardization, validation, is subject to errors, lack of completeness, etc. Not good.

I'm not sure the most straightforward way to improve this. We currently use HR software that lacks an API. And I'm not even certain it contains all the necessary data fields, or input validation.

In an ideal world, I could just demand we find new software that has an API and data validation. But I don't see that happening. But maybe that's the only solution.

Otherwise, I'm trying to think of a simple solution where users can view the current database (id, employee name, start date, department, etc) ... and possibly either make direct changes, or at least propose changes to be approved. Some kind of input that at least has basic checks and validations, timestamps changes and who made them, etc.

I know this is a pretty open-ended question, but what is a possible straightforward solution?

Ideally I'd like to not have to piece together some application from scratch, at least not too much.

Can't be the first business here trying to track department headcounts and costs month to month.

At the end of the day, a lot of this stuff does have to entered by a human at some point. If someone changes departments, that's largely an abstraction. A computer system may not know if someone went from working to marketing and is now working in sales. A human being will have to enter this change somewhere (ideally a dedicated application, but not necessarily) -- the person, the change, the effective date. What's really needed is better validation controls and business rules that either an application, I myself, or another developer, can impose.

For instance --- non duplication of employee IDs. (both not giving Mark and John the same ID, nor giving Mark two different IDs). An employee only working associated/ under 1 department at a given time. Easy views and management of changes (aka the leave date was incorrect, change history and timestamp of updates would be useful). Maybe some decent payroll/ accounting software can be leveraged. I mean, this isn't reinventing the wheel, I wonder how mature/ Fortune 500 companies manage this. Probably dedicated software, but eh.

The problem is the database needs this information. Currently it receives it from a garbage Excel spreadsheet. I'm trying to find ways to make sure the information is not garbage. This needs to be done in the form of a restriction, or alert, to the end user before the garbage is submitted down the chain. At the very least maybe I can send them an Excel sheet that has its own validation in cells, but that's comically unsuitable long term.


texts: Why this system table doesn't appear on dictionary table?

texts:
How you can see I am the sys:

SQL> show user;
     USER is "SYS"

When I search the table in the dictionary the dictionary returns no rows:

select table_name from dictionary where table_name like '%AUDIT_UNIFIED_ENABLED_POLICIES%';

no rows selected

but if I issue the desc command I receive the structure of the table

desc AUDIT_UNIFIED_ENABLED_POLICIES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_NAME                                          VARCHAR2(128)
 POLICY_NAME                                        VARCHAR2(128)
 ENABLED_OPT                                        VARCHAR2(7)
 ENABLED_OPTION                                     VARCHAR2(15)
 ENTITY_NAME                                        VARCHAR2(128)
 ENTITY_TYPE                                        VARCHAR2(7)
 SUCCESS                                            VARCHAR2(3)
 FAILURE                                            VARCHAR2(3)

Why does this happen?


texts:
Schema does not list all fields, but only the relevant ones

User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' field in the receipts' table)
After a receipt has been approved, the amount will be added to the account_balance of the user
When a user makes a purchase from the site, the amount will be deducted from his account_balance
Also, a user can request a withdrawal of his funds

Table: users

id | name | email | password | account_balance | created_at

Table: receipts

id | user_id | payment_method | transaction_datails | amount | status | created_at

Table: purchases

id | user_id | product_id | amount | created_at

Table: withdrawals

id | user_id | amount | created_at

Question:

Is it better to have a transactions table that stores all receipts, withdrawals, purchases in one table?


texts: SQL Server 2008 R2 replication high delivery latency

texts:
I am seeing an unusually high delivery latency between our distributor and subscribers and I do not understand why.

We have in this configuration 3 sql servers using transactional push replication to replicate data from one master server to two reporting servers.

We have 9 publications.  The distribution agent for most publications are showing under 5ms, but one is show as 2000+ms to both subscribers.

The suspect publication has only 4 small articles (tables) that rarely, if ever, change.  I've checked and each table has an primary key.

I've also checked the @status parameter for each article according to the MS KB: The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

I'm tempted to start dropping articles to find out if one particular table is the culprit.

Does anyone have any suggestions as to what I can look at?


texts:
I have an issue on pgAdmin 4.14: I can't open the servers anymore. Whenever I open it I have this never-ending wheel.

Furthermore, I have this weird pop-up that I can't close.

I was able to reach my servers correctly yesterday.


texts: fetch all data having a Price BETWEEN 100000 AND 10000000

texts:
I have a table named articles with a column named price.

When a user on my web app selects both Price Min and Price Max, I need to perform a query like this:

$priceMin = 100000;
$priceMax = 10000000;

$query = 'SELECT * FROM articles WHERE price BETWEEN $priceMin AND $priceMax';

However, when the selected Price Min is <= 100000 and Price Max is >= 10000000 (10 Million), I don't have any output with a Price greater than 900000 even though rows with values above 900000 do exist.

The table is defined like this:

CREATE TABLE articles (
 id int(11) NOT NULL AUTO_INCREMENT,
 name varchar(100) NOT NULL,
 price varchar(100) NOT NULL,
 source varchar(255) NOT NULL, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8


texts:
Don't naturalize surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow them to work with the value (perhaps to search), you have effectively given the key business meaning. This in effect naturalizes the key and thereby negates some of the advantages of surrogate keys.
http://www.agiledata.org/essays/keys.html

Don’t “naturalize” surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow users to work with the value (perhaps via search), you have effectively given the key business meaning. The exposed key in our database could then be considered a natural key in someone else’s.
https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html

And yet, every web framework/ORM I am familiar with (e.g. Ruby on Rails) exposes the surrogate key in the URL which is natural key that users share, send, save, bookmark, etc.
In light this of this recommendation, what are the best practices for handling web URLs?


texts: Oracle query performance when filtering results

texts:
I am currently working on a query for a front end application, which is experiencing weird performance issues when filters are applied on large sets of data. Here is a link to an SQLFiddle of the simplified query.

The query is executing in about 1-2 seconds when fetching all rows. (limited only by rownum) A similar performance can be observed when filtering by amounts above a certain threshold (e.g. total > 1000). However, when filtering below a threshold (e.g. available < 1000) the query is taking forever to calculate the results.

As a general note all related table columns are indexed.

Is there perhaps a better way to perform the calculations shown in the example?


texts: How make an SSAS Tabular model case sensitive?

texts:
I need to make my tabular model case sensitive.

Currently my tabular model is case insensitive, how do I change my existing cube to be case sensitive?


texts: Azure SQL Databases: High number of SA connections with "BRKR TASK" and "TASK MANAGER" commands

texts:
Found out that all our Azure SQL Databases (pricing tiers S1-S2) have high number of connections/sessions from SA

SA - BRKR TASK - around 60 sessions
SA - TASK MANAGER - around 20 sessions

Here are two questions:

1. What are those ?
2. Why so many connections from them on Azure SQL databases ?

The reason I ask is sometimes it causes error

"The request limit for the database is (paste a number here) and has been reached"

Regards,


texts: import TSV file into MySQL table with SET datatype column

texts:
Is it possible to use LOAD DATA INFILE to load data into a MySQL/MariaDB table, where one column uses the SET datatype? My columns are separated by tab, my set members by comma.

If so, how do I specify the separator for the individual set elements?


texts: Is it safe to have Mongo replicas on single server?

texts:
I am planning a minimum setup for a low traffic discussion forum. Everybody says to use replicas because otherwise I am in a risk of data loss. I see an advantage that I can add new more powerful node without a downtime. While I was reading Mongo performance best practices, I noticed this sentence:

With appropriate sizing and resource allocation using virtualization
or container technologies, multiple MongoDB processes can safely run
on a single physical server without contending for resources.

What do you think? Is it safe? If the server or disk dies then all replicas are down.


texts: FOR XML could not serialize the data because it contains character (0x0000)

texts:
I have a big query (if necessary I will post it here) and I'm getting this error:

  Msg 6841, Level 16, State 1, Line 1
  FOR XML could not serialize the
  data for node 'NoName' because it contains a character (0x0000) 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.

The only part I use FOR XML is here:

WHERE
    (CodFuncionario = Results.CodFuncionario)
FOR XML PATH(''), TYPE).value('(./text())[1]',
    'VARCHAR(MAX)'), 1, 2, '') AS [Experiencia]

But, what is node noname? and how can I look for this value: (0x0000)

This is one of the subqueries (the only part I have FOR XML):

SELECT
    [CodFuncionario],
    STUFF
    (
        (
            SELECT
                ' / ' +
            CAST
            (
                [DescFuncao] + '-' +
                [DescTempoExperiencia]
                AS VARCHAR(MAX)
            )...
FROM
    [Linked_Server].db.dbo.tblFuncionarioExperiencia T0
INNER JOIN
    [Linked_Server].db.dbo.tblFuncao T1 On T0.codFuncao = T1.CodFuncao
INNER JOIN
    [Linked_Server].db.dbo.tblTempoExperiencia T2 ON T0.CodTempoExperiencia = T2.CodTempoExperiencia
WHERE
   (CodFuncionario = Results.CodFuncionario)
   FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS [Experiencia]
  FROM
      [Linked_Server].db.dbo.tblFuncionarioExperiencia Results
  GROUP BY
      CodFuncionario) as T2

  On T0.CodFuncionario = T2.CodFuncionario

Left Join...


texts: Find the number of rows in a table from metadata

texts:
I want to write a single select statement to determine the number of rows in a set of tables in Sybase IQ.

In SAS, I write this as

CREATE TABLE dsnobs as
SELECT libname,memname,nlobs, datetime() format DATETIME16. as timestamp
FROM dictionary.tables
WHERE (libname=upcase('MY_LIB') & memname IN ('TABLE_1', 'TABLE_2', 'TABLE_3'));

For your information,

a SAS libname is something like a table creator or table space
the dictionary are similar to IQ SYS tables
memname is just the name of the table

Can I do the same thing in IQ?

An approximate solution, or an indication on the space used would suit me too.


texts: How do you handle adding nullable columns to each other in a query?

texts:
Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0).
Is there a better way to handle this, or is this the standard practice?


texts: Why is SQL doing an index scan and seek with the same XML query structure?

texts:
I've set up a test to benchmark xml performance in SQL server.
Test Setup

One Million rows of data
XML defined Column with primary key
A primary XML index
A secondary XML index on the path
XML data is similar in format but with variable tag names in each document

Table and Index Design
CREATE TABLE [dbo].[xml_Test]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [varchar](50) NULL,
    [JSON_Data] [varchar](max) NULL,
    [XML_Data] [xml] NULL,
    CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC)
);

ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [DF_xml_Test_GUID]  DEFAULT (newid()) FOR [GUID];
ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC);

CREATE PRIMARY XML INDEX [PK_xml] ON [dbo].[xml_Test]
(   [XML_Data]);

CREATE XML INDEX [IX_xml_Path] ON [dbo].[xml_Test]
(   [XML_Data])
USING XML INDEX [PK_xml] FOR PATH;

Sample XML Schema
<data>
  <id>3812</id>
  <guid>E3735046-1183-4A79-B8EE-806312B533D6",</guid>
  <firstName>John</firstName>
  <lastName>Doe</lastName>
  <tel>123-123-1234</tel>
  <city>Toronto</city>
  <prov>Ontario</prov>
  <Q.49.R.47>14325</Q.49.R.47>
  <Q.1>14326</Q.1>
  <Q.9>143257</Q.9>
  <Q.25>14328</Q.25>
  <Q.50>14329</Q.50>
  <Q.51>14330</Q.51>
  <Q.30>14331</Q.30>
  <Q.22>14332</Q.22>
  <Q.100>14333</Q.100>
  <Q.70.R.4>1</Q.70.R.4>
  <Q.43>14335</Q.43>
  <Q.3>14336</Q.3>
  <Q.84.R.21.L.19>1</Q.84.R.21.L.19>
  <done>1</done>
</data>

When I go to query the table with two different values, I get a very efficient seek for one result and a scan for the other that takes orders of magnitude longer to complete.
I'm unsure as to why the second query is not able to do a seek on the index.  I think it may have something to do with the element not being in the index to start with but even then I don't know why a scan would be necessary unless the index really didn't index all paths.
Execution Plans via Paste the Plan
Query A
select ID, 'Query A'
from xml_test
where XML_Data.exist('(/data/Q.70.R.4)[1]')=1

Query B
select id, 'Query B'
from xml_test
where XML_Data.exist('(/data/Q.61.R.15)[1]')=1

As you can see, Query A takes 5ms to execute compared to almost 15s for query B.
What are the reasons that the same query format but with different input would yield such an obviously inefficient execution plan?
Aside

I was also using this to test against JSON with identically formatted JSON data and my results show that the identical JSON query as XML Query A took 22,344 ms (22 seconds!) of cpu time (3,419 ms clock time) to run.
If the XML index can work reliably with a seek operation, it is clearly the more performant read option contrary to what I have researched and read online.
When querying over a large amount of data, I would not recommend JSON at all inside SQL server other than for blob storage or where you have a small dataset, which is to say, don't use it in production for searchable data that should be indexed.  There are other good services designed for that.


texts: Re-subscribing to a merge publication after edits made offline

texts:
Hypothetical Question - To help scenario planning.

Let's say I have a 2008 R2 Merge Publication with 5 active subscribers.  The publication is deleted and all subscriptions are removed.  A week or so goes by and data is continually added or updated on the local databases that used to be subscribers.

A week later a new publication is created on the original database and the 5 local boxes are all given new subscriptions.  What happens to the data that was added locally during the week no subscription existed?

A: Is it uploaded to the publisher once the new subscription is created?
B: Is the data from the week offline deleted?
C: Is the data from the week offline ignored by the publisher (and hence other subscribers)?
D: Something else happens

If C?  Is there a way to mark those records for replication so they can be brought up to the publisher, and ultimately the other subscribers?


texts: Select like and unlike record pattern SQL

texts:
Lets assume I got this table, that got file pathes :

id    file
 1     /etc/file.txt
 2     /etc/.delete.file.txt
 3     /etc/secondfile.txt
 4     /etc/four.txt
 5     /etc/five.txt
 6     /etc.delete.five.txt

I want to get all the files that are not deleted,

So if there is a file : .delete.file.txt -> I don't want to select both .delete.file.txt and also file.txt (always the prefix is .deleted)

Expected result after query :

id    file
 3     /etc/secondfile.txt
 4     /etc/four.txt

How can I do that?
(Using SQLITE)


texts:
What is the use of log_slow_verbosity = query_plan parameter in my.cnf ?

I got an error when installing MySQL in my machine because of this parameter.


texts: Multi Master replication vs Single-Master performance

texts:
I'm trying to wrap my head around a hypothetical scenario.

Imagine we have a very write-heavy distributed database and sharding is not an option.

I wonder if / how is multi-leader replication (write-write) more efficient than single-leader scenario (write-read) since write-write has overhead to sync databases and propagate writes to the other master(s) ending up with the same number of write operations, ultimately.

In which cases is multi-leader replication for write-heavy applications considered more performant than single-leader and in which cases it is not?

I understand the question is broad and nuanced but would be happy to read some thoughts on the subject.


texts: Isolation level of queries to system objects, object catalog views etc

texts:
Today an alter procedure statement was called.  The alter statement was made, the correct DB using statement was in place, and immediately SSMS returned 'completed successfully' to the DBA.  The proc being run in production continued to show old results however.  After verifying with users the DBA right clicked on the object and generated schema, and the resulting script from SSMS showed the original code not the code as scripted in the alter statement.

The window with the alter statement was still up, so F5 was hit again.  Same results.  'Completed Successfully' displayed by SSMS, while scripting the stored proc out reveals old version still in place.

After hitting F5 repeatedly, say maybe 20-30 times, the users finally started reporting that the code was working, and scripting out the object revealed the alter statement had finally taken effect.

When modifying schema definition, I know SQL Server takes schema locks to ensure that active queries and processes are consistent, but that didn't seem to be the case here as SSMS was reporting 'completed successfully' almost immediately.

Does SSMS report success when modifying some schema objects before it actually is successful?  What is the isolation level of querying changes to programmable object definitions, i.e stored procs.  I would think altering any object would delay the success result from SSMS until the schema lock had cleared and SQL Server had actually committed the change to the system object storage on which the catalog views depend.  For example an alter table definitely does.  This can take a significant amount of time depending on what changes are meta data only versus what changes require row by row conversions.  The command is not reported as successful on an alter table statement until actually done.

Is that not the case for programing objects such as stored procs and functions?


texts:
I need to select the oldest(min) due datetime for each InternalOrder and then filter these against some datetime for example NOW().
As you can see in schema image, I designed the schema as one InternalOrder has multiple Deliveries and each Delivery has multiple DeliveryRev(revisions), as I need to have whole trace of inputed data. I always look at the latest revision, if that one is deleted the whole delivery should not be considered.

Table rows:

InternalOrder: 2970
Delivery: 3258
DeliveryRev: 12272

I select the newest DeliveryRev via joins and filter these, which have due datetime field filled and also are not deleted.

I have working query (first one), but it is not exactly, what I need, since I can get the result with InternalOrder, which have oldest(min) due under specified threshold(not selected) and another one, which is over (in the result). In this case I do not want that InternalOrder in the result, which I solved using having instead of where, but this query is taking ages.

Am I doing something wrong with HAVING, or how can I optimize/change to get right result?

This query is fast (40ms), but not getting right result.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
    AND s10_.due > NOW()
GROUP BY s9_.internal_order_id
ORDER BY minDue ASC

This query is slow (25s), but the result is alright.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
GROUP BY s9_.internal_order_id
HAVING minDue > NOW()
ORDER BY minDue ASC


texts: Microsoft SQL Server Connection Coloring

texts:
I've been using the Redgate Query Tab Coloring feature (under SQL Prompt's Experimental Features) and I really like it.  Somehow I've never known until today that this feature is built in to SSMS for Status Bar coloring.

My question is, with either Redgate, SSMS, or SSMS Tools Pack, is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?  Our server is running SQL 2012 SP2 and SSMS clients are either 2014 or 2012 SP2, also.

Let's just say there are some error-prone co-workers that have been known to execute queries against an instance thinking it's TEST or DEV and it ends up being PROD.  We're all human I guess, but anything that can help would be really great.  And I don't want to rely on the other people to set the coloring settings (because I know some of them won't).  I know, I know, everyone shouldn't have PROD access, but that's another battle I need to fight.

Red Gate implementation:

Built-in SQL Server Management Studio implementation:


texts:
One of our MySQL servers died but somehow we were able to recover all the files from it. This server was a slave at one time however we stopped it being a slave so we could use it for housekeeping tasks and reports, etc.

Can someone tell me how to recover with this?

I now have these files and a backup of my.cnf

    ll /root/home/recovered/mysql/
    -rw-r----- 1 root root          117 Jun  1 09:30 rabney-relay-bin.000001
    -rw-r----- 1 root root           26 Jun  1 09:30 rabney-relay-bin.index
    -rw-r----- 1 root root 113055367168 Jun  1 10:42 ibdata1
    -rw-r----- 1 root root   1073741824 Jun  1 09:33 ib_logfile0
    -rw-r----- 1 root root   1073741824 Jun  1 09:33 ib_logfile1
    drwx------ 2 root root         4096 Jun  1 09:30 mysql
    drwx------ 2 root root        16384 Jun  1 09:30 production_copy
    -rw-r----- 1 root root   3253518648 Jun  1 09:32 slowquery.log
    drwx------ 2 root root         4096 Jun  1 09:30 test

I've been reading articles on Google regarding this but not sure how to do it yet.


texts: Securely manage and dynamically create multiple databases

texts:
Background

I have inherited a system that has several hundred databases.

The primary "Clients" Database represents all clients with several tables like:
  WebUsers = username | password | server_id
  Servers = server_id | ip_address |

The remaining "ClientInfo" databases are intended for one client. Tables like:
  Customers = id | first_name | last_name

I am not a DBA. I'm a web developer who inherited a rough built system. So I apologize ahead of tie if this question seems silly.

The process

The client visit a login server: login.example.com. This server has access to the "Clients" database. They login to their account which takes them to the correct web application server, sass1.example.com, that has credentials found in the "Servers" database and grants them a secure session.

I think this is a pretty basic SASS web application setup. Think sales-force, get-satisfaction, that kind of thing.

My Problem

All databases are managed under a single database login. So the login server technically has access to all the client databases. And each application server has access to all databases. The only limiter being a single string in the session that identifies the user.

From a security standpoint it is my understanding that these databases should each have their own credentials. So that if one is breached via code injection remaining databases are secure.

For example my code logs into the client db using environment variables. Then selects the database name that is stored in the secure session. A code injection could easily submit a query that changes the active database.

What is the secure way to manage this system?

My rough plan was to have the login server point to the "Clients" database through a unique db user that has access to only that database. Then I would store the db user/password for each clients database in a "ClientList" table.

Then on login the client specific database credentials would be pulled from an encrypted value in the "server" table. So the session would check if it's in a valid login state then instead of the string pointing to a database name it has an id that points to the "ClientList" table that has encrypted login credentials to just their database.

But this doesn't seem much different than just managing all db's on the same login since the clients database still has all the passwords. A code injection would have access to the decrypt algorithms and just log in with different credentials. What is the right (secure) way to manage this system?


texts:
I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables.

Which JOIN type should I use to do this?

SELECT SLD.SLD_Number,
       SLD.Date_Received,
       SLD.Time_Received,
       [Payment table].Date_Received,
       IIf(Payment_Status = 'NOT PAID', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr1,
       IIf(Payment_Status='PENDING', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr2,
       DateDiff("d",[Invoice table].Date_Received,
       [Payment table].Date_To_Payment) AS Expr3,
       DateDiff("d",[Order table].Date_Quotes_Requested,SLD.Date_Received) AS Expr4,
       DateDiff("d",[Order table].Date_Of_Order_Number,SLD.Date_Received) AS Expr5,
       IIf(Payment_Status = 'PAID', DateDiff("d",[Invoice table].Date_Received, [Payment table].Date_Authorised_Paid)) AS Expr6,
       IIf([Invoice table].Date_Received=NULL,'TRUE','FALSE') AS Expr7,
       Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM   (([Payment table]
INNER JOIN [Invoice table]
ON     [Payment table].SLD_ID = [Invoice table].SLD_ID)
INNER JOIN [Order table]
ON     [Payment table].SLD_ID = [Order table].SLD_ID)
LEFT JOIN SLD
ON     [Payment table].SLD_ID = SLD.SLD_ID;

RIGHT JOIN is bringing up this error:

  Join expression not supported. (Error 3296)


texts:
I am using Ubuntu release 14.04, MySQL 14.14 Distrib 5.5.43.

To reset the MySQL root password, as described in the official MySQL doc How to reset the root password, I need to stop the MySQL server.

Using htop before killing the server, I see the following related processes:

Then, as indicated, I stop the MySQL server with the following command:
kill $(cat /var/run/mysqld/mysqld.pid).

But, a few seconds later, all associated processes are automatically restarted under different PIDs:

How can I stop the server without it restarting new processes each time ?


texts: Forcing view to use certain execution plan

texts:
Can I somehow force an execution plan to a view?

I came across to parameter sniffing and I cannot add option recompile to a query because it is generated by DevExpres, so the only option is to change the view


texts: How to design database for market place website

texts:
I do not have much experience in table design. My goal is to create a multi-vendor market place website with laravel  that meet the requirement below:

Support many kinds of products (10 producs) such as (Car, Home, ..., Book) that each kind of product has a different or same set of field, like:

Car has title, color, model, ..., price.
Home has title, place, #bedroom, ..., price
...
Book has title, author, ..., price

I have two options for design our database as following:

Create one table for all product with same fields and one table for each product with different field.
Create table for each product with all fields.

The question is which of two options is better and some help for how to design standard database?

Thanks for sharing your knowledge.


texts: DB mail sending attachment of size 64 KB only even after increasing maximum file size limit

texts:
I am facing an issue with DB mail functionality. I am using SQL Server Agent to trigger mail on success or failure. While sending this, I am also providing an attachment, which consists of some query-generated report.

The problem here is I am able to send mail with attachment but the attachment size is fixed to 64Kb only. I have increased maximum attachment size limit from Database mail configuration, still the same problem exists.

I am executing following lines of code:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profileName,
    @recipients = @recipientsList,
    @body = @Body,
    @subject = @Subject ,
    @body_format = 'HTML',
    @query= @Query,
    @query_attachment_filename=@EmailFileName,
    @attach_query_result_as_file=1,
    @query_result_no_padding = 1,
    @query_result_separator= ' '


texts: SQL Cluster failover doesn't work after applying SQL Server 2016 Service Pack 2

texts:
I tried to apply SQL Server 2016 Service Pack 2 on the clustered instance of SQL  Server (SQL Server 2016 SP1 CU7, 2 nodes, active-passive architecture). We did this patching previously on the other cluster with similar configuration and it went fine. I started with the passive node, the installation succeeded but, when I tried to move SQL role to the newly patched node (in order to start patching the second node), the failover failed. In the cluster events log I found the corresponding error:

The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.

As per error message I checked system event log on passive node and I found few consecutive errors at that time:

6/9/2018 7:08:07 PM

Cluster resource 'SQL Server' of type 'SQL Server' in clustered role 'SQL Server (MSSQLSERVER)' failed.
Based on the failure policies for the resource and role, the cluster service  may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

6/9/2018 7:08:07 PM

The Cluster service failed to bring clustered role 'SQL Server (MSSQLSERVER)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

6/9/2018 7:08:07 PM

Clustered role 'SQL Server (MSSQLSERVER)' has exceeded its failover threshold.  It has exhausted the configured number of failover attempts within the failover period of time allotted to it and will be left in a failed state.  No additional attempts will be made to bring the role online or fail it over to another node in the cluster.  Please check the events associated with the failure.  After the issues causing the failure are resolved the role can be brought online manually or the cluster may attempt to bring it online again after the restart delay period.

and eventually the critical one at 6/9/2018 7:08:37 PM

The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.

Unfortunately, this information is not very informative for me, so I tried to dig into cluster log, there is definitely more information there, but I still struggle to find the root cause and solution.

You can find the full cluster log here: https://1drv.ms/u/s!Aiz8LIBP787C6ABO8aqfN9ayyFC0

I think the key to solving the issue is the cluster log, but the error messages are not clear to me. Perhaps some of you guys spotted this issue before?

I think it's worth to mention we run our SQL Clusters on Azure and we use S2D technology for cluster storage purposes. I mentioned about this as we can see following erros in cluster log:

000009c8.00002e04::2018/06/09-17:07:35.889 ERR   [API] ApipGetLocalCallerInfo: Error 3221356570 calling RpcBindingInqLocalClientPID.
000009c8.00002e04::2018/06/09-17:07:35.889 INFO  [CftCache] Querying whether disk {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} supports cache state [ReadWrite]
000009c8.00002e04::2018/06/09-17:07:35.889 INFO  [CftCache] Can't query seek penalty storage property for device [Disk: Id {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} # 2 #paths 0 #id 0 H      ], error 31.
000009c8.00002e04::2018/06/09-17:07:35.891 INFO  [CftCache] Disk [Disk: Id {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} # 2 #paths 0 #id 0 H      ] Path \\?\Disk{166faa04-8d1a-4901-e0b4-a7c75bdd54e3} candidate? true cacheSupported? true supportStatus 0
000009c8.00002e04::2018/06/09-17:07:35.892 ERR   [API] ApipGetLocalCallerInfo: Error 3221356570 calling RpcBindingInqLocalClientPID.

Perhaps it's an issue with storage? But if that's the case why it has been revealed by sql patching?


texts: Unable to add columns in a particular table

texts:
I am trying to add 4 columns on a table using the below command

alter table table_name
add
(column_1 number(4),
 column_2 varchar2(10),
 column_4 number(4),
 column_5 varchar2(10));

The table contains 18,057,945 records and has a BEFORE UPDATE trigger. The alter command gets stuck and returns after 2 hours with the below error.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate 216 bytes (kxsbbinfo,adl context curMsgBuf)

Note that I ran the same alter statement on 3 other tables which are basically snapshots of the same table with similar index,trigger etc. and it took around 0.06, 0.03,0.05 seconds to run the alter command.

I have checked other questions like this.. but it provides no insight on this particular challenge I am facing, and I have not added any not-null/default value in my alter statement. There are no locks on the table and also there is no active cursor as well on the given table...

Please advise. I have a test environment and a production environment. The funny thing is that the alter for this particular table is getting stuck and giving the same error in both the environments.

Please advise what I can check/do!! Thanks in advance.

I also analyzed/gather stats for this table in between tries to alter it!!!

ANALYZE TABLE table_name ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS SIZE 75 SAMPLE 100 PERCENT;

EXEC dbms_stats.gather_table_stats('xx','table_name' ,cascade=>TRUE);


texts: Show all stored procedure definitions (content) for editing

texts:
Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little.

I need to list the definitions (code) of all my stored procedures, so that I can start working through all 450 of them and adding in semi-colons to work toward v2014 standards.

I found the following code here and adapted it slightly:

SELECT
    obj.Name AS SPName,
    REPLACE(modu.definition, 'CREATE PROC', 'ALTER PROC') + 'GO' AS SPDefinition
FROM
    sys.sql_modules modu INNER JOIN
    sys.objects obj ON modu.object_id = obj.object_id
WHERE
    (obj.type = 'P')
ORDER BY
    obj.name;

The code above is run using the SA account.

I assumed that this provided exactly what I needed, but, I've just noticed that somebody on MSDN is claiming it doesn't show the full text as expected.

When pasting the results into the SSMS editor, I have 46,000 lines of code to work through, so its not apparent to me if anything is missing.  I'd like to be sure I've started this process correctly before wasting a day in the editor.

Therefore, I am reaching out to you pro's to ask if this is a genuine shortcoming of this approach, or is there an alternative method rather than one-by-one?


texts: SQL Server Client configuration via file

texts: Is it possible to configure a SQL Server CLIENT (cliconfg) via file? I'm looking forward to something like tnsnames.ora in Oracle world.


texts: Trigger before update a fields in a table

texts:
I want to know how can I code a trigger that verify a fields before updating it. For example I want to prevent a date update, let say, not to modify records with date before '2020-01-01'.

Thanks in advance.


texts: How to export mysql databases with different ENGINE?

texts:
I'm setting up MySQL Cluster. As it uses ENGINE=NDBCLUSTER, how could I export my databases from MySQL Server specifying the ENGINE?

Is there a way to do this?

All my tables are MYISAM


texts: Paralleled SSIS package flatlining at 20% CPU

texts:
I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter period of time, it seems to flatline at around 20%.

Looking at Task Manager I can see that all cores are being used during the test runs.

It is a Windows 2016 Server running 16 CPU cores on a VM with 32GB of RAM. SQL Server 2016.

Cost Threshold for Parallelism is set to 50 and MAXDOP is set to 0 on the SQL instance.

My question is, is there a setting on the SQL Server instance or in the SSIS package that will allow me to make the CPU run at 100% and save CPU idle time?


texts:
I have a database where tables are like:

tblCustomer(UserID [Primary Key],Facebook,Twitter,PhoneNum);
tblSales(InvoiceID [Primary Key],CustomerID [Foreign Key],ProductID [Foreign Key]);

I'm importing some paper-based records and they're in chronological (time) order, with following columns:

(Customer's)FaceBook,Twitter,PhoneNum,ProductID;

For some reason we don't have existing ID systems for customers so UserID will be auto-generated at import-time. In my scenario, any of Facebook, Twitter or Phone number can uniquely identify a customer so I have unique index for each of them to enforce uniqueness constraint.

I created a view to facilitate data import:

viewDataEntry(FaceBook,Twitter,PhoneNum,ProductID);

A common case is that customer's Facebook (or other contact method) appears in multiple sales records. A trigger is created to handle such cases:

CREATE TRIGGER
ON dbo.viewDataEntry
INSTEAD OF INSERT
AS

BEGIN TRY
INSERT INTO dbo.tblCustomer(Facebook,Twitter,PhoneNum)
SELECT Facebook,Twitter,PhoneNum FROM inserted;
END TRY

BEGIN CATCH
IF ERROR_NUMBER() != 2601 --To ignore uniqueness violation exception
THROW;
END CATCH

DECLARE @UserID INT;
SET @UserID = (SELECT UserID FROM dbo.tblCustomer AS O,inserted AS I WHERE (O.PhoneNum = I.PhoneNum OR O.Facebook = I.Facebook OR O.Twitter = I.Twitter));

INSERT INTO dbo.tblSales(CustomerID,ProductID)
SELECT @UserID,ProductID FROM inserted;

GO

Intended outcome is:

If duplicate Customer records are imported, drop duplicate, insert into sales table only;
If new Customer record is imported, create record for both customer and sales table.

However whenever duplicate values are entered I run into Error 3910 or 3616, which means the transaction is un-committable. I think it's because insertion into customer table need to be rolled back and I know that I cannot rollback part of the transaction while keeping the remaining part (which is, unfortunately, the intended outcome).

I found MERGE statement but it has too many restrictions (like WHEN MATCHED must be followed by UPDATE and DELETE).

Please kindly provide any working solution.


texts: Alter table modify column on a huge table containing more than 25 million records

texts:
I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts

What is the most efficient way to alter a column definition in a table with millions of rows

and

ALTER TABLE on a large table with an indexed column

My current strategy is

Drop all non clustered index
Drop clustered index
Alter table modifcy column varchar (32) -- column is char(20) presently
Recreate clustered index
recreate non clustered index

Does it make sense. Is there a faster method do it ?

Edit :

Table Data Size : 16.7 GB
Total rows : 25835155
avg row size : .64 KB
8  Non Clustered Index
Yes,column I am changing is the second column in a 2 column NC index


texts: Why does an UPDATE against a table with an INSTEAD OF UPDATE trigger appear to do a clustered index insert, as well as a clustered index update?

texts:
I'll start with a very simple example: two tables, both with the same schema, clustered on PK, but one of which has an INSTEAD OF UPDATE trigger:

CREATE TABLE Standard
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

CREATE TABLE InsteadOf
(
    PK  UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
    V   INT NOT NULL
)
GO

INSERT Standard (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
INSERT InsteadOf (PK, V) VALUES ('1E58B555-B073-471E-B576-4B09C8E18976', 0)
GO

CREATE TRIGGER TR_InsteadOf_Update ON InsteadOf INSTEAD OF UPDATE
AS
BEGIN
    DECLARE @PK UNIQUEIDENTIFIER
    DECLARE @V INT
    DECLARE @cursor CURSOR
    SET @cursor = CURSOR FOR SELECT PK, V FROM Inserted
    OPEN @cursor

    FETCH NEXT FROM @cursor INTO @PK, @V
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE InsteadOf SET
            V = @V
        WHERE PK = @PK

        FETCH NEXT FROM @cursor INTO @PK, @V
    END
    CLOSE @cursor
    DEALLOCATE @cursor

END
GO

If I view the query plan for an update against the standard table, I get the expected clusted index update:

UPDATE Standard SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'

However if I perform a similar update against the table with the trigger, I get what appears to be a clustered index insert, as well as the clustered index update:

UPDATE InsteadOf SET
    V = 1
    WHERE PK = '1E58B555-B073-471E-B576-4B09C8E18976'

Why is this? I can see the clustered index update I expected later in this query plan (query #4), but why do I get this extra insert at query #1?


texts:
Recently I have started to implement database mirroring and faced with some trouble. According to Books online, full database backup and T-Log backup must be restored in the mirror instance in WITH NORECOVERY mode:

  The mirror database must be in the RESTORING state for mirroring to
  work. When preparing a mirror database, you must use RESTORE WITH
  NORECOVERY for every restore operation. Minimally, you will need to
  restore WITH NORECOVERY a full backup of the principal database,
  followed by all subsequent log backups.

But when I start mirroring, I receive following exception:

  "Database "MyDB" cannot be opened. It is on the middle of
  restore(Microsoft SQL Server, Error 927)

Can anyone explain, whether I misunderstood something ?


texts:
I already saw this question here:
https://stackoverflow.com/questions/1795176/change-mysql-data-directory
but it's not very helpful.

What I want: to move my sql data folder to my different place (/media/data)
What I did:

a) sudo service mysql stop

b) mv /var/lib/mysql/ /media/data/

c) sudo vim /etc/mysql/my/cnf:

section [mysqld]:
   datadir = /var/lib/mysql/ - > datadir = /media/data/lib/
section [mysqld_safe]:
  added:  datadir = /media/data/lib/

d) sudo vim /etc/apparmor.d/usr.sbin.mysqld:

added:
  /media/data/mysql/ r,
  /media/data/myql/** rwk,
commented out:
  #/var/lib/mysql/ r,
  #/var/lib/mysql/** rwk

e) sudo /etc/init.d/apparmor reload

f) sudo service mysql start
What I got:

a) tail -f /var/log/mysql/error.log:

120215  1:30:01 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
120215  1:30:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120215  1:30:01  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

b) Even when sometimes log is clean I cannot connect to server:
   mysql -u root -p
   Enter password:

   ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run  /mysqld/mysqld.sock' (2)

Please help..


texts:
Unfortunately hard disk of my system is damaged, now I recovered my database. But it cannot be attached again to SQL management studio 2005.
The error is:

myfile.mdf is not a primary database file. (SQL server , error 5171)


texts: Database design for storing historical nmap data

texts:
I am designing a database to store nmap data. I want to store two basic types of data:

Information about the nmap scans themselves (e.g. start and end times, nmap version used, command line arguments used)
Information about the hosts on the network (e.g. host names, IP and MAC addresses, open ports, services running)

The primary purpose of my application is to generate a "map" of the network; essentially, a listing of which hosts are up, which hosts are down, which ports are open on which hosts, etc. For this part of the application, all I care about is the current state; in other words, the results of the latest nmap scan. However, my application also needs to do some historical reporting, like generate a graph of host OS over time.

I read this StackOverflow answer about storing historical data and it seems logical: only track the current state in your main tables and use audit tables for historical reporting. So I would have, for example, a hosts table with the following columns:

host_id
name
ip_address
mac_address
status

and a ports table with the following columns:

port_id
host_id
number
status

Every time I run nmap, I would truncate the hosts and ports tables and insert the new results.1 The corresponding audit tables would track all of the changes over time so I could do historical reporting. Easy enough.

The problem(s)

What I can't figure out is the best way to store the information about the nmap scans themselves. If I followed the above structure, I might have a scans table with these columns:

scan_id
start
end
version
arguments

and a corresponding audit table. But the scans table would only ever contain one row (the most recent scan). This seems utterly pointless. There would also be no need for a scan_id column.

Okay, drop the audit table just for scans. Every time I run nmap, create a new row in the scans table and increment the scan_id. Problem solved.

However, now I want to generate a graph of number of hosts on the network over time. This would require me to join the scans table with the hosts_audit table. But now I need to add scan_id as a column to the hosts and ports tables in order to do the join. If I do this, however, every row in the hosts and ports tables would have the same scan_id, since those tables are truncated every time I run nmap. Hmmmm.

This is my first time designing a database from the ground up, and something doesn't smell right. I want the hosts table to reflect the present state of the network, but truncating the table every time I run nmap seems...unorthodox. I want to be able to connect data from the scans table with data from the hosts table, but can't figure out a way that makes sense. What exactly is wrong with the design I've outlined above and how can I improve it?

I don't think simply updating rows would work; for example, if a particular host is decommissioned, it would no longer appear in the nmap results but without truncating, would stay in the hosts table indefinitely. However, it feels a little odd to be truncating all my tables every few minutes...


texts: Getting error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

texts:
I am getting the error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. for the following query.
After checking some questions here I was able to know the date format of my sql server by running SELECT GETDATE() and what I got was 2015-06-11 15:58:01.473 which seems like the format i am using to insert the data. What am I doing wrong?

SET IDENTITY_INSERT cms_clients ON
INSERT INTO cms_clients (id, user_id, firstname, lastname, middlename, pseudoname, dob, photo, careplan1, careplan2, gender, program, csscentre, managing_csc, unique_ref_no, cur_weight, tar_weight, benf_ent_number, med_number, drugs_alert, prescriber_particulars, special_considerations, vaccinations, dom, doa, status, address, medicare_expiry_date, telephone, date_of_annual_health_care_plan, first_language, interp_req, religion, religious_considerations, diagnosis_and_associated_health_conditions, communcation, challenging_behaviours, medicare_number, pension_number, private_health_insurance, person_responsible, person_res_name, person_res_address, person_res_telephone_h, person_res_telephone_w, person_res_telephone_m, person_res_email, decision_making_function, relative, guardian_name, guardian_address, guardian_telephone_h, guardian_telephone_w, guardian_telephone_m, guardian_email, health_number, entry_date, exit_date, mobile_no, spoken_lang, known_address, written_directives, significant_other, additional_info, school_program, school_address, school_contact, school_phone, school_transport, families_social, families_email, families_snr, families_supervisor, families_fsa, families_address, families_phone, families_fax, curfew_fsa, curfew_mpr, youth_allowance, pocket_money, bail_cond, details, court_date, transport_via, location, pract_name, pract_relationship, pract_phone, pract_fax, pract_address, pract_email, approved_name, approved_relationship, approved_phone, med_expiry_date) VALUES
(1, '', 'test', 'test', '', '', '2015-06-05', '', '', '9', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(180, '', 'test 2', 'test', '', '', '2015-06-05', '', '', '10', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(181, '', 'test 3', 'test', '', '', '2015-06-05', '', '', '8', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(182, '', 'Ted', 'Mosbey', '', '', '2015-06-10', '', '', '10', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '0000-00-00 00:00:00', '2015-06-09 05:54:50', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00');


texts: Adding new stored procedure to the exisiting database using connectors?

texts:
The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy.

The question arose when I've tried to find out the way of adding new stored procedure to my database via those connectors. I've found a dozens of examples of how to call an existing stored procedures from a database but none of how to actually add (create) a new one by them.

I kept on googling and have found text() construct in sqlalchemy, this one enables me to write raw sql code, so IMO, can be used for adding a new stored procedure to the existing db.

Generally speaking there always is a direct way to do so by using mysql shell or workbench editor. But how can this task be achieved with mysqldb python connector(if at all)?

Is it a good/weird practice to add new stored procedure that way?


texts: Why am I getting the An expression of non-boolean type specified in a context where a condition is expected, near ',' error

texts:
This is my code:
SELECT *
FROM mytable
WHERE (Permit,Stampdate) IN (SELECT Permit,MAX(Stampdate) AS Stampdate
                             FROM mytable GROUP BY Permit)


texts: Why aren't primary key / foreign key matches used for joins?

texts:
As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to join (like natural join does with names). Why is that? If you've already defined a relationship between 2 tables with a pk/fk, why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

EDIT: to clarify this a bit:

suppose I have a table1 and a table2. table1 one has a foreign key on column a, which references to the primary key on table2, the column b. Now if I join these tables, I'll have to do something like this:

SELECT * FROM table1
JOIN table2 ON table1.a = table2.b

However, I already defined using my keys that table1.a references to table2.b, so it seems to me that it shouldn't be to hard to make a DBMS system automatically use table1.a and table2.b as the join columns, such that one can simply use:

SELECT * FROM table1
AUTO JOIN table2

However, many DBMS don't seem to implement something like this.


texts:
I have a SQL Server 2012 instance running on a 2 Node Failover Cluster Instance. Both nodes are VMs running Windows 2012 Server R2.
The FCI has four shared LUNs: 1 for data files, 1 for log files, 1 for TempDB and 1 for Quorum.

The Backup volume however is not a shared LUN. There’s a separate disk drive added on the VM (Node 1) for a backup drive. The other VM, Node 2 has its own local disk drive for backup location.

I use Ola Hallegren’s script for DB maintenance.

I am thinking about adding another shared LUN in the FCI and set it up as the backup location for both nodes. My thinking is that, this way if the SQL Server role fails over to Node 2, the backup files will still get saved on the same shared LUN for backup files.

Question:
Are there any drawbacks, implication or gotchas with this setup? Any insights is much appreciated! Thank you.


texts: MySQL - Problems caused by SQL Security definer or invoker

texts:
I have a view used to be defined as:

> **CREATE
>     ALGORITHM = UNDEFINED
>     DEFINER = `user1`@`localhost`
>     SQL SECURITY DEFINER VIEW `schema1`.`view1` AS
>     select
>        ...
>     from
>      ...
>     where
>      ...**

The user1 has all privileges to the schema1's objects. And if I, as user2,

> select * from view1

, it works fine. But If I do:

   SELECT * FROM schema1.view1 INTO OUTFILE '/tmp/file.txt'  FIELDS TERMINATED BY '|'

I got error saying:

  Error Code: 1356. View 'schema1.view1' references invalid table(s) or
  column(s) or function(s) or definer/invoker of view lack rights to use
  them   0.000 sec

If I changed the definition of view from "SQL SECURITY DEFINER" to "SQL SECURITY INVOKER", and do the above query again (and I also have full privileges to the objects of the schema1), it works.

Can anybody explain why this happens? Thank a lot.


texts: Problem adding a new node to a SQL Server 2012 Failover Cluster

texts:
I tried to add a new node to SQL Server (2012 Standard) Failover, that is already installed on another node in Windows2012 Failover Cluster
I have an issue with non editable field wiht input for SQL Server Agents Account Name
there could be the same AD name as for SQL Server Database Engine (e.i.)
my account access haven't AD admin privileges
access, account and setting for SQL Server Agent on parent node are accesible and settable


texts: Using double write buffer is 8x slower in SSD (compared with 2x~3x in HDD)

texts:
I understand the double-write-buffer enhances the reliability of data, so it makes transactions slower. But it is amazing that the slow down is such severe in the newest Samsung 980 pro (M.2 PCIe 4.0, which is about 400$ for 1TB).
Workload: https://github.com/Percona-Lab/tpcc-mysql
Configurations: other parameters are defaults.
CPU: AMD Ryzen 3900XT
MEM: 64GB, 3200MHz
OS: Ubuntu 20.10, all disks are ext4
MySQL: 8.0.22
The boxplot on the left:

Y-axis: max Latency (ms) of 95% of the transactions in 10 sec period (the lower the better). Each rectangle represents 20 repeated runs (in 200 sec). The red short line in the middle of the rectangle represents the median of the 20 data. The whole rectangle represents for the distribution of the 25%-75% data. The small circles are outliers that can be ignored.
X-axis: There are 8 rectangles, the first 4 are the latencies on the 4 different disks (which are shown in the legend on the right boxplot) respectively when --innodb-doublewrite=OFF; the last 4 are when --innodb-doublewrite=ON.

The boxplot on the right:

Y-axis: normalize the data in the left boxplot by divining the median of the first 4 rectangles (makes the red short line of them =1). In this way, I can compare the relative performance drop (i.e., how many times do the performance drop, this is what I wrote in the title: "8x" and "2x~3x") after turning on innodb-doublewrite. As shown, the red line in the Samsung 980's rectangle are ~8 times larger after using the doublewrite buffer.
X-axis: same as the boxplot on the left.

Why does this happen? Did I hit a performance bug?
Thanks!


texts: What is the purpose of a user's default database?

texts:
As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct?

I've never seen a connection string without a database specified, is it even possible? Why would anyone want to do this? It just makes it less transparent to which database you are connecting.

In other words is there any reason to choose anything else than master as the default database?


texts: MySQL Master-Slave Replication Cross Database

texts:
I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and
Replicate-wild-do-table=database_name.% ,But i am not able to get cross Database updates from master to slave . Please Help me to resolve the issue.


texts: Should I avoid adding a clustered key to a table with high throughput

texts:
We have a SQL Server solution that has a table dsStaging.Audit that stores audit records created by a third party transactional database.  We use these audits to synchronise CRUD operations from the third party system into our SQL database.

CREATE TABLE [dsStaging].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditDateTimeZone] [datetimeoffset](3) NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
    [SyncExecutionId] ASC,
    [TransactionId] ASC,
    [TransactionSequence] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

When the audits are processed, I want to move the audit records into a separate table Processed.Audit, ready for deletion after x days.

CREATE TABLE [Processed].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Processed_Audit] PRIMARY KEY NONCLUSTERED
(
    [AuditDate] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

My main aim in moving audits out of staging and into processed is performance.  I need to make sure that the staging table is locked for as short a time as possible, so that any unprocessed audits can be processed as quickly as possible (fewer audits in the staging table = much faster processing).

We're looking at around 1.5m audit records going through this process every hour in batches of about 10k.

The process for moving audits will fire roughly every 20-30 seconds.  The process for deleting Processed.Audit records will execute every hour and delete 1 hour's worth of audits from X days ago (typically around 7 days).

Should I turn the Processed.Audit table into a clustered index?

Minimum version to be supported: SQL Server 2012 Standard Edition.


texts: Negative InnoDB Write Log efficiency in mysqltuner.pl results

texts:
I noticed something strange in results from mysqltuner.pl.

I see this in mysqltuner output from three of my four servers with MariaDB 10.1.12:

  [OK] You have the latest version of MySQLTuner(1.6.5)

  [OK] Currently running supported MySQL version 10.1.12-MariaDB

  [!!] InnoDB Write Log efficiency: -79.30% (-8928 hits/ 11258 total)

What does this mean?
How number of hits can be negative?

EDIT

It can be concluded from the contents of mysqltuner.pl script that

  InnoDB Write Log efficiency = (Innodb_log_write_requests -
  Innodb_log_writes) / Innodb_log_write_requests

It means that InnoDB Write Log efficiency is negative when innodb_log_writes is greater than innodb_log_write_requests. Still, I don't know what this means in practice.

Is this bad that innodb_log_writes > innodb_log_write_requests?
How should I interpret this "efficiency"?


texts: Best way to add a new column to a large table? (MySQL - MyISAM)

texts:
Here's more info on the table that needs to be altered:

15GB size MyISAM table with Index around 5GB
Over 100 million rows
MySQL 5.1 on RHEL 5 with 4GB RAM
Currently live online

What's the best way to add a couple of new column (minimum 2 columns) to a large table like this? With minimum downtime. Just using direct ALTER table commands would take hours if not a day or two to finish.

Additional question: Will it affect the length of time needed to add new columns if the new column default value are set to NULL instead of having a 'pre-defined' value?


texts:
A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx 400 million records from this table. This is running as I type. I notice that I am not seeing any drop in my hard drive space but I am seeing the memory drop on system table queries, I am running to get table size. The database is using "Simple Recovery Model".

There are many questions similar to this with responses saying you need to shrink the database. But they go on to explain how bad/scary this is to do because of fragmented data etc.

Since the database should not be this size. Is it still bad for me to shrink it?
This is a production database. If I shrink it will it cause downtime or lock the database?
In SQL Server Management Studio you have two options for shrink, database or files. Given my situation what would be the best option?
is there a rule on the percentage of free space a DB should have?

Even reading the tag description of shrink makes me not want to do it. Is there another way?


texts: Does adding an auto_incrementing number to a MySQL partitioned table that is not part of the primary key, cause consistency issues?

texts:
I have a table that has a primary key which does not include an auto_incrementing number. The table needs to be partitioned (on that primary key) for the overall strategy of reducing contention points.

I need to add an auto_incrementing number outside the primary key/partitions, but I am concerned about how MySQL will be able to keep the right sequenced number, if under the hood, all partitioned tables are in essence different tables and as that auto_incrementing number cannot be in a separate unique index.

So the question is, will I get consistency issues with the sequence doing this or does MySQL have a way to keep it accurate?


texts: What is the best way to automatically create a snapshot of the table at the certain time?

texts:
I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts).

What is the best way to do it?

Edited: Table that I have to snapshot has about 50.000.000 records. Only 5-20% of them are changing over the day.

Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis.

Please tell me if it is a god idea or not?


texts:
I have a table with multiple records for each ID.

For example, there may be three or more records for one Class ID): one record with a status of "approved" and two more records with status NULL, or one record with the approved field populated with some numerical value and another two records will approved set to null, or one record with the requested field set to some numerical value and two other records with requested set to be null.

As output, I want one record with status "approved", and with approved and requested populated with their numerical values.

The question via an example:

Now, I want a single record with column T approved  and COLUMN W,X WITH 12 VALUE

EXAMPLE:


texts:
I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below).  We're a pretty small company with growing needs due to major growth on the business side.  Losing 1 days of data at this point would cost tens of thousands of dollars vs a couple hundred this time last year.  Until I can migrate this DB platform to a different solution where data mirroring occurs with major redundancy like SQL Azure, what is the best thing I can do to get more frequent backups?  Does this script below force the DB to be offline?  Can I run this script with users interacting with the DB?

USE CompanyCRM;
GO
BACKUP DATABASE CompanyCRM
TO DISK = 'D:\CRMBackups\CompanyCRMCRM.Bak'
   WITH FORMAT,
      MEDIANAME = 'CompanyCRM_Backup',
      NAME = 'Full Backup of CompanyCRM';
GO

Update

Wow, obviously a much more dedicated DBA community over here than on SO.  Thanks for the feedback so far.  Only thing missing is the "hows."  I have shown the SQL command above that I'm using to do daily backups, but the incremental log backup examples are MIA.  This is not a large DB, it currently runs on SQLExpress.  When I say HA or SQL Azure, I'm specifically referring to the architecture in place that we do not have as a small business.  This instance is currently running on our ONLY server.  If that server crashes, our time to recover becomes a sticking point.  This is why SQL Azure becomes attractive.


texts: Does taking a log backup, truncating the log, and then deleting the tlog backup break the LSN chain?

texts: We have avamar tool taking backups in my environment. The backup plan avamar does is only taking trans backups once a day. I am convincing them to take the trans backup more frequent. But in the meanwhile, I have to deal with large log sizes because of this infrequent trans backups. Hence to reduce the log sizes, I am taking a quick trans backup followed by a truncate log size. Now I want to know if doing that breaks the log chain for avamar and it forces it to take full back up instead. Remember, I am deleting the trans backup file immediately after I truncate the log sizes. Does the fact that I deleted the trans backup file breaks the log chain for Avamar? Any input is appreciated.


texts:
I try to create a system to auto approve proposed posts by popular vote. I am currently evaluating what portions of this I can do within the DBMS and if it is sensible. I use PostgreSQL and I can go up to the newest version if that helps me.

My database structure would look somwhat like this:

CREATE TYPE state AS ENUM ('write', 'vote');

CREATE TABLE post
(
  id SERIAL NOT NULL CONSTRAINT post_pkey PRIMARY KEY,
  title VARCHAR(100),
  state state
);

CREATE TABLE proposal
(
  id SERIAL NOT NULL CONSTRAINT proposal_pkey PRIMARY KEY,
  post_id INTEGER CONSTRAINT proposal_post_fkey REFERENCES post,
  text TEXT
);

CREATE TABLE accepted
(
  id SERIAL NOT NULL CONSTRAINT accepted_pkey PRIMARY KEY,
  post_id INTEGER CONSTRAINT accepted_post_fkey REFERENCES post,
  text TEXT
);

CREATE TABLE vote
(
  proposal_id INTEGER CONSTRAINT vote_proposal_fkey REFERENCES proposal,
  user_id INTEGER,
  PRIMARY KEY(proposal_id, user_ID)
);

Here you can find a SQLFiddle for that layout

My goal is now to build on or more queries to copy the proposal to accepted and set the state to write according to these rules:

If the number of votes for a proposal is more higher than a threshold n
If there is exactly one proposal with the highest vote count

My question is now whether this can sensibly be done within PostgreSQL or should be done outside and also if you could point me into the right direction for the queries. Also I wonder if it would make sense to track the votes on a column on the proposal to save on the JOIN and COUNT()


texts: Joining very distant tables in the absence of a direct relationship

texts:
Consider a database of the following structure:

The schema above is just made up, but assume that this is what the DBA inherits and can't be modified further. Then, hypothetically, if the DBA is asked to provide a selection of the two most distant tables table1 and table7, at least to me, this appears to be no easy feat.

Question: If we wanted to SELECT table1.name, table7.name using a JOIN wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the answer change if the tables were not just 6 tables apart as in my example, but rather were 1,000 tables apart?

I'm not sure if SQL has a special tool for this, or if we just use n-1 number of AND clauses. If the latter is the case, I'd imagine we'd want to write a batch or python file to automate a selection with 999 AND's.


texts:
I'm running my database on a Sybase ASE 15.7 server.
A simple delete statement fails without printing any error , I presume there is no permissions problem as I used to delete rows by the past.
Here is what I did from an isql console.

1> select count(*) from LOGIN where login="336959686@login.com"
2> go

 -----------
       1

(1 row affected)
1> delete from LOGIN where login="336959686@login.com"
2> go
(0 rows affected)

The row wasn't deleted, am I missing something?

LOGIN table definition :

1> sp_help LOGIN
2> go
 Name  Owner Object_type Object_status Create_date
 ----- ----- ----------- ------------- -------------------
 LOGIN xxx   user table   -- none --   Dec  7 2010  2:13PM

(1 row affected)
 Column_name            Type     Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ---------------------- -------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
 login_oidval           numeric       8   16     0     0              0 NULL         NULL      NULL             NULL                            0
 login                  varchar     165 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 creation_date          datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 last_modification_date datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 idp_oidval             smallint      2 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
Object has the following indexes

 index_name index_keys         index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local
 ---------- ------------------ -------------------- ----------------------- ---------------- -------------------- ------------------- ------------
 PK_LOGIN    login_oidval      nonclustered, unique                       0                0                    0 Dec 10 2010  7:10PM Global Index
 login_idx1  login, idp_oidval nonclustered, unique                       0                0                    0 Jun 10 2011  2:55PM Global Index

(2 rows affected)
 index_ptn_name       index_ptn_seg
 -------------------- -------------
 PK_LOGIN_448001596   default
 login_idx1_448001596 default

(2 rows affected)
No defined keys for this object.
 name  type       partition_type partitions partition_keys
 ----- ---------- -------------- ---------- --------------
 LOGIN base table roundrobin              1 NULL

(1 row affected)

 partition_name  partition_id compression_level pages row_count segment create_date
 --------------- ------------ ----------------- ----- --------- ------- -------------------
 LOGIN_448001596    448001596 none                863     25497 default Feb 12 2014  3:01PM

 Partition_Conditions
 --------------------
 NULL

 Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)              Ratio(Min/Avg)
 ----------- ----------- ----------- --------------------------- ---------------------------
         863         863         863                    1.000000                    1.000000
Table LOB compression level
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
 ------------ -------------- ---------- ----------------- ------------ -----------
            0              0          0                 0            0           0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
 ------------------------- --------------------- -------------------
                        15                     0                   0
(return status = 0)


texts: Now to normalize table when position in group uniquely identifies entity

texts:
I need to model charts such as the one displayed below...

... and came up with the following:

For the sample chart I originally showed..

charts.name is "Custom transactions", charts.y_legend is "Amount", x_legend is "Customer Group"
series.name is "2007" for position 0 and "2006" for position 1, categories.name is "Foreign" for position 0, "Specialty Stores" for positions 1, etc.
chart_values.value will be the value for a given series and category such as $14,000,000 for series "2007" and category "Foreign".

As seen, the unique series can be identified solely on idSeries and position, (and similarly for categories, however, I will no longer be saying so), and thus series.idseries is redundant.  Does this violate the 1st, 2nd, 3rd, or Boyce and Codd Normal Form?

Regardless of normalization purity, I am also concerned (yet don't know why) with the implications of using composite key charts_idcharts/position as series primary key.  For my application, a backend configuration page will allow the user to add/remove categories and series, rename them, change the position order, etc.  To change a series name, the client can provide the chart ID and series position along with the new desired value.  To change position, the client could provide an array of the previous positions in a given order [2, 0, 1, 3].  Are there any negative implications of using position in a group in an entities primary key (other than needing to change the primary key upon changing position)?


texts: DB-Library not loading, SQL Client Configuration

texts:
When I open Client Network Utility (cliconfg.exe) on my laptop it generates an error that

  C:\WINDOWS\SYSTEM32\NTWDBLIB.DLL is either not designed to run on
  Windows or it contains an error. etc.

It then opens. Clicking on the DB-Library Options tab that generates the error again. When it does display it states the Version: as "Not Loadable".

What are some methods I could use to try and fix this please?

I believe this may be related to issues I'm having with a 3rd party exe using VB6 that is having issues connecting to one of our SQL2008R2 boxes (it connects to other servers of this version fine).

Edit: I did fix the connection issue, but this not loadable error was not the cause of it so it would be nice to fix. (The actual issue was expired credentials stored in Credential Manager)


texts:
Last week I updated mysql max_connections to 1000 from 151 using

use mysql;
set global max_connections = 1000;

Today there was a power outage at the hosting company and the servers shut down and were then restarted.  After restart, the max_connections variable was reset to 151.

Is this default behavior?  How can I prevent max_connections global from getting reset?

Reference:

MySQL 5.7


texts: Is there a JDBC Driver for Microsoft SSAS Tabular Databases?  Is so where can they be downloaded?


texts:
Questions

I got a 12 GB machine with apache & myqsl, i have may apache max_client set to 150 will this cause some bottlenecks with the 50 max_connection in mysql?
What value should I set mysql max_connection or apache max_client to balance and still have optimize performance for my site?

Thank you.

Please see my mysql config and mysqltuner result.

[mysqld]
innodb_file_per_table
datadir=/var/lib/mysql
tmpdir=/var/lib/mysqltmp
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
table_cache=1024
table_definition_cache = 1024
thread_cache_size=16
back_log=100
max_connect_errors=10000
open-files-limit=20000
interactive_timeout=30
wait_timeout=60
max_connections=50
skip-name-resolve

# Slow Query Log Settings
log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=5
log-queries-not-using-indexes

# Global, Non Engine-Specific Buffers
max_allowed_packet=16M
tmp_table_size=1768M
max_heap_table_size=1768M
query_cache_size=32M

# Per-Thread Buffers
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=8M
join_buffer_size=32M

# MyISAM
key_buffer_size=64M
myisam_sort_buffer_size=64M

# InnoDB
innodb_log_file_size=100M
innodb_buffer_pool_size=1512M

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=65535

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated
+InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 48M (Tables: 124)
[--] Data in InnoDB tables: 2G (Tables: 179)
[!!] Total fragmented tables: 39

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 14h 27m 2s (20M q [41.828 qps], 1M conn, TX: 275B, RX:
5B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 3.3G global + 42.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 5.4G (46% of installed RAM)
[!!] Slow queries: 6% (1M/20M)
[OK] Highest usage of available connections: 74% (37/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/31.1M
[OK] Key buffer hit rate: 99.9% (11M cached / 7K reads)
[OK] Query cache efficiency: 68.1% (10M cached / 14M selects)
[!!] Query cache prunes per day: 42514
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 73669
[OK] Temporary tables created on disk: 6% (136K on disk / 2M total)
[OK] Thread cache hit rate: 99% (155 created / 1M connections)
[OK] Table cache hit rate: 74% (974 open / 1K opened)
[OK] Open file limit used: 0% (453/65K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
[!!] InnoDB data size / buffer pool: 2.3G/1.5G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 32.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 2G)


texts: Randomizing table contents and storing them back in the table

texts:
I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read the site collections in a serial manner, first few thousands of rows belong to first site collection, next few thousands belong to second site collection, and so on.

I have another app that reads this table in a sequential manner. However, this way I end up sending HTTP requests to the same site collection for a longer time.

I know I could get random results from the table in my second app. But, that is not an option. I cannot change the way the second app works.

Now, the question is: How can I take all rows in the table, shuffule them and store it back in the table?

Update: SQL Server 2008 R2 is my database Server


texts: How can I in Oracle discover the average number of rows affected per transaction?

texts: Our Oracle server is a big cluster of three very large individual instances. I want to try to understand the average number of rows per transaction. How can I go about doing this?


texts: Join Multiple Derived Tables, Correctly in MySQL?

texts:
I'm trying to achieve a full outer join in MySQL

EDIT : As per comments:

Data is

Need data with for question_id in(44,45,46,47,48,49)

Desired Output is :
Query generated using golang:

select @s:=@s+1,q1,q2,q3,q4,q5,q6

from (SELECT * FROM

(SELECT * FROM

(select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1
   left join
 (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2 on t1.u1 = t2.u2

 left join
 (select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3
on t2.u2 = t3.u3

 left join
(select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4
 on t3.u3 = t4.u4

left join
 (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5
 on t4.u4 = t5.u5

  left join
 (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6
 on t5.u5 = t6.u6

) X

UNION

 (SELECT * FROM

 (select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1

  right join
 (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2
 on t1.u1 = t2.u2

 right join
(select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3
on t2.u2 = t3.u3

 right join (select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4
 on t3.u3 = t4.u4

 right join (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5
 on t4.u4 = t5.u5

right join (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6
on t5.u5 = t6.u6

) ) ORDERED_ALIAS , (SELECT @s:= -1) AS s   ORDER BY u1=0 DESC


texts:
I am trying to restore Oracle DB from a backup taken recently. While restoring I got the following error:

Failing sql is:

DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN  DELETE
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found

I searched in the net for some possible solution, one of them suggested to exclude the statistics(EXCLUDE=INDEX_STATISTICS) while exporting or importing the database.
Please let me know if it's safe to exclude statistics while restoring a recently crashed/corrupted database.