texts: MySQL Stored Procedure Compiles Without Error (I want the error to occur)

texts:
I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: -

Valid statement: insert into dept (dept_id, dept_name) values (1,'Development');

Statement with intentional typo: insert into dept (dept_id, dept_names /*Note plural*/) values (1,'Development');

My dept table does not contain a column called dept_names (plural) - correct column is dept_name (singular). Upon compiling the procedure, I was expecting MySQL Workbench to show an error message indicating non-existent column, but no such error occurred. The error message was shown when I executed the procedure. Coming from Oracle background, this is somewhat unusual for me. Oracle performs all schema checking (i.e. bad column names) when we Save stored procedures.

Now comes the question: What can I do to make MySQL Workbench (or even mysql server command line prompt) show any and all error messages when I save stored procedures and not wait until I run/execute/call them?

Thanks!

Additional Info 1: Just wanted to add that the "bad" INSERT statement above is wrapped within a IF statement. This caused stored procedure to continue executing without errors for many months until one fine day it entered the IF statement. Error message Unknown column 'dept_names' in 'field list' was received immediately after entering IF statement upon executing the "bad" INSERT.

Additional Info 2: Of course, runtime errors can wait until I actually run/execute the stored procedure. My "bad column" example above is not a runtime error so I would like to see the error when saving/compiling the stored procedure.


texts: What is the best way to divide data for random groups?

texts:
I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was:
SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) AS bigint) % 31)
Questions:

There is any better way to do this except CHECKSUM (different values on differents COLLATE) and own functions ?
There will be any difference If i will use SELECT CAST(HASHBYTES('MD5',[keyColumnFromTable]) AS tinyint) % 31 ? I was reading that CAST to tinyint is taking into consideration last byte of data. Will be any affectt on randomness ?


texts:
I have an sql server database and I want to make a mirror copy of it on my local computer. I tried to use the copy database wizard but it fails. It seems me to look at the event log for the reason but all it says in the event log is

Package "CDW_4" failed.

so I have no clue why it is failing.

Is there another way to do this?


texts:
I need to write a transaction for SQLite, that adds a blog post and links it to content tags, and it seems to be a bit too complicated for me. Here's an example schema:

CREATE TABLE blogpost(
       id INTEGER PRIMARY KEY,
       content TEXT
);

CREATE TABLE blogpost_tag(
       blogpost_id INTEGER NOT NULL,
       tag_id INTEGER NOT NULL,
       PRIMARY KEY (blogpost_id, tag_id),
       FOREIGN KEY (blogpost_id) REFERENCES blogpost(id),
       FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
);

CREATE TABLE tag(
       id INTEGER PRIMARY KEY,
       name TEXT
);

I have the blog post content string and two tag strings, and now I need to write these into the database in an atomic transaction.

The blog post content row need to be inserted into blogpost table.
Let's assume that one of the tags already exists in tag table. Other one is new and needs to be inserted.
Blogposts and tags have a many-to-many relationship, so I need to insert two rows to blogpost_tag table.

How do I write this transaction?


texts: Update trigger issue throwing errors on IF statements

texts:
I have created a trigger, basically what I want it to do is update or insert a record to another table.
Two table I have got:

MVF_Transport_Register: Main table
MVF_Transport_Acc: table trigger need to update.

When a record is updated on the Main table, I want to the trigger to check if that records exists on the MVF_Transport_Acc.

If it does only update the Record_status column.
If the record is not in MVF_Transport_Acc then insert the new record.

This is what I have written but I am getting the following error when trying to deploy it:

Msg 156, Level 15, State 1, Procedure UpdateTransport_TEST1, Line 36
Incorrect syntax near the keyword 'END'.

Here's the trigger:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
IF UPDATE (Record_Status)

BEGIN
IF (i.Order_No=c.Order_No )
UPDATE c
set Record_Status=i.Record_Status
FROM dbo.MVF_Transport_Acc AS c
JOIN inserted as i
ON i.Order_No = c.Order_No
JOIN deleted as d
ON i.Order_No=d.Order_No
OR (i.Record_Status <> d.Record_Status)
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)
ELSE

INSERT into dbo.MVF_Transport_Acc
([Order_No], [Record_Status], [Notes], [Transport_Supplier], [Surcharge], [Vendor_No], [Pallets], [Amend_Pallets],[Price], [Input_Date], [Amend_Price], [Transport_Invoice_No],[Transport_Job_No], [Amend_Date], [Report_Price],
                         [Return_Price], [Report_Date])

SELECT  i.[Order_No], i.[Record_Status], i.[Notes], i.[Transport_Supplier], i.[Surcharge], i.[Vendor_No], i.[Pallets], i.[Amend_Pallets],i.[Price], i.[Input_Date], i.[Amend_Price], i.[Transport_Invoice_No],i.[Transport_Job_No], i.[Amend_Date], i.[Report_Price],
                         i.[Return_Price], i.[Report_Date]

        FROM   [dbo].[MVF_Transport_Register] c
        INNER JOIN inserted i on
       c.Order_No = i.Order_No
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)

END IF

END


texts: Database is happily available even during log growth?

texts:
I’ve been creating a process to go through all our databases and analyze the log structure, backup, shrink, and resize appropriately taking operational requirements and VLF sizing/amount into account. My code is working beautifully, but I encountered something in testing today I wasn’t expecting.

I have a good sized DB with a 104GB log and around 900 VLFs. I backed up the log and shrank down to next to nothing during a period of low activity. I then began to grow it back out to it’s original size in 8GB iterations, keeping VLFs around 512MB. During regrowth (somewhere between 48GB and 56GB), I was curious about DB availability and started running random SELECTS against different tables. Results returned without issue. I then created a table in the DB and looped a bunch of inserts.

I was surprised when the log continued to grow and the table creation and inserts completed without issue or delay. I was under the impression the DB would somehow be locked down, blocking DDL and DML, until regrowth completed. Of course, considering the circular nature of the log, I can see how this would be a false assumption. But I can’t find anything on the web one way or the other.

Can someone shed some light or point me int he right direction on what is going on? Thanks!


texts: How to create a CSV file which imports data from an Access Database on a Weekly Schedule?

texts:
I have very little experience in database management but have come across a problem I am unable to find a solution to online. I currently have access to a database which is updated in real time with the most recent sales figures for a company. In simple terms it looks like this on 01/09/2016:

Sales
50

I wish to make a CSV report which takes the sales levels from the live database each week and inserts them as a new row in the CSV file. In order to track the report date, I had planned to add a new column named 'Report Date' with the date of report update listed for each observation taken from the live database.

The desired output would have a format similar to

Report Date     Sales
01/09/2016      50
08/09/2016      70
15/09/2016      79

It would be good to know what people think is the best method to produce this type of dataset. If there are any options that involve SQL that would be good, but open to any method that does the job.Let me know if any more information is needed!


texts:
I have an rman backup of 11.2.0.2. Also I have installed 12c on a separate server. Can I move my backup to 12c. if yes?
Please guide me how to do it?
Appreciate your comments and answer.


texts:
In Windows Server Failover Cluster Manager I can see "possible owners" under "Other Resource" and also under "Server Name".

I have a 3 node AlwaysOn Availability Group with 2 servers in synchronous mode and 1 in Asynchronous Mode for disaster recovery.

Under the possible owners under "Other Resource" I can see the ones in Synchronous mode is checked but under the 'Server Name' all are checked.

Will appreciate if any one can tell me the difference between possible owners listed under "Other Resource" versus those listed under "Server Name".


texts: Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

texts:
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common expressions up out of the CTE, etc. It's often claimed to be a behaviour required by the SQL standards.

CTEs are definitely an optimisation fence in PostgreSQL ... but is this required by the standard, or actually just implementation detail?

For example, these mailing list posts claim or suggest that it's standard:

http://www.digipedia.pl/usenet/thread/11566/101385/

After mentioning it in a comment I was asked where it's specified - and after a look at the only draft of SQL:2008 I have access to I'm not having much luck finding it.

I haven't yet intensively studied the standard, so I'm hoping for a suggestion from somebody who has: Is the optimisation fencing of CTEs in PostgreSQL actually required by the standard? And if so, where's it specified? Or are the statements on the Pg mailing list in error?

See also the thread CTE optimization fence on the todo list?.


texts:
insert Table1
OUTPUT inserted.ID, s.[Id]
INTO @Inserted
select
s.[Field1],
s.[Field2],
from
@TableVariable s

why is s.[Id]  marked by red line ? Is it non-correct syntax to save value in @Inserted table variable ?


texts: Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

texts:
I have a table which is created this way:

--
-- Table: #__content
--
CREATE TABLE "jos_content" (
  "id" serial NOT NULL,
  "asset_id" bigint DEFAULT 0 NOT NULL,
   ...
  "xreference" varchar(50) DEFAULT '' NOT NULL,
  PRIMARY KEY ("id")
);

Later some rows are inserted specifying the id:

INSERT INTO "jos_content" VALUES (1,36,'About',...)

At a later point some records are inserted without id and they fail with the error:
Error: duplicate key value violates unique constraint.

Apparently the id got defined as a sequence:

Each failed insert increases the pointer in the sequence till it increments to a value that no longer exists and the queries succeed.

SELECT nextval('jos_content_id_seq'::regclass)

What is wrong with the table definition? What is the smart way to fix this?


texts: How to remove unnecessary symbol in the column in SQL Server 2008

texts:
my database got some data with unnecessary symbol in front it like ""

How do I remove the front symbol. I try to do like this but it do not work.

SELECT DISTINCT REPLACE(NEW,'.','')
FROM [FixedAsset Control].[dbo].[NewViewReport]

Can anyone help me on that.


texts: User Permissions Messed Up following a Backup -> Restore Operation

texts:
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management Studio).

All went OK but now I find I can't login to any of the databases using the SQL Server account that continues to work on the old RDBMS. My windows-authenticated login still works fine incidentally.

I had this idea the users and permissions would all be seamlessly duplicated onto the new database server but it seems something has gone wrong somewhere. I would appreciate comments/suggests/offers of help ;-)


texts: Hello experts I need to update next column value with divisible by 100


texts: How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known

texts:
I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has one of these forms:

All tables `test_table1,test_table2.

Nothing. So NULL will pass to the procedure.

I've read about Bind variable and the significant role it has in preventing injection and improving performance and I want to use it in my procedure.The problem is that I do not know how exactly I should change my procedure to use them specially in this situation when number of variables are not known. When you know the exact number of variables, you write execute immediate actual_query using var1,var2 and you know you will exactly have 2 variables.
create or replace procedure bind_variable_test(i_tables varchar2,
                                               i_cid    number,
                                               o_result out sys_refcursor) is
actual_query varchar2(1000) := '';
begin

-- this is the base query
actual_query := 'select *
            from z_test_a t1
              inner join z_test_b t2
               on t1.id = t2.id';

-- check input parameter " i_tables "
if i_tables like '%test_table1%' then
  actual_query := actual_query || ' ' || 'inner join test_table1 t3 on ' ||
                     't3.id = t1.id ' || 'and t3.cid = ' || i_cid;
end if;

if i_tables like '%test_table2%' then
  actual_query := actual_query || ' ' || 'inner join test_table2 t4 on ' ||
                     't4.id = t1.id ' || 'and t4.cid = ' || i_cid;
end if;

-- debug results
dbms_output.put_line(actual_query);

-- execute
open o_result for actual_query;
end;

After reading DBMS_SQL manual , I tried to edit my code and I did this:
create or replace procedure z_bind_variable_test(i_tables varchar2,
                                             i_cid    number,
                                             o_result out sys_refcursor)       is
actual_query varchar2(1000) := '';
c            pls_integer;
begin
-- this is the base query
actual_query := 'select *
              from z_test_a t1
               inner join z_test_b t2
            on t1.c_num = t2.c_num ';

-- check input parameter " i_tables "
if i_tables like '%test_table1%' then
  actual_query := actual_query || ' ' || 'inner join test_table1 t3 ' ||
                'on t3.C_NUM = t1.C_NUM ' || 'and t3.cid = :c_id';
end if;

if i_tables like '%test_table2%' then
  actual_query := actual_query || ' ' || 'inner join test_table2 t4 ' ||
                'on t4.C_NUM = t1.C_NUM ' || 'and t4.cid = :c_id ';
end if;

-- get cursor
c := dbms_sql.open_cursor();

-- parse the SQL
dbms_sql.parse(c, actual_query, DBMS_SQL.NATIVE);

-- using bind variables
dbms_sql.bind_variable(c, ':c_id', i_cid);

-- execute
o_result := dbms_sql.execute(c);

-- close the cursor
dbms_sql.close_cursor(c);
end;

But it just won't work! I want to learn how to use bind variables correctly but don't know whether I'm in the right path.
Update : The error I get is PLS-00382:Expression is of wrong type. And the problem is for this part o_result := dbms_sql.execute(c);


texts: Confusion about CPU time and Elapsed Time Calculation

texts:
I have already searched on different online forums already, but I haven't got any clear answer on this.

The problem is I have a query which has CPU time greater 4 times greater than Elapsed time. I also have a query in which Elapsed time is greater than CPU time. I am not able to understand why is it so. Can you please shed some light, what actually happens in both cases?

An example is my query has CPU Time 4000ms while Elapsed time is just 1000ms and one query has elapsed time 2ms while CPU time is 0ms.

According to following post:

https://stackoverflow.com/questions/6419312/sql-server-query-tuning-why-cpu-time-is-higher-than-elapsed-time-are-they-rel

higher CPU time and lower elapsed time is due to hyper-threading or multi cores utilization by SQL server but I am not able to find any authentic information regarding that. Another confusion is the reverse case. How is it possible that elapsed time is for example 4ms while CPU time is 0ms?

Any detailed answer regarding this would be highly appreciated.


texts:
A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...

I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.

I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.

My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?

Thanks


texts: Indexing or changing one-to-many relationship

texts:
I have these two Microsoft SQL Server 2005 tables:

ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004

Objects (ObjectID [PK])
1
2
3

Table ObjectLicenses has a primary key on column ObjectLicenseID. Table Objects has a primary key on column ObjectID.

I want to return a list of all LicenseType's, by Object ID. However, this takes a long time, possibly due to lack of index(es)...currently, I use a "FOR XML PATH" query to do this:

SELECT      O.ObjectID

            ,STUFF( (SELECT      '; ' +
                                 CONVERT(VARCHAR(12), ROW_NUMBER() OVER
                                 (ORDER BY OL.ObjectLicenseID)) +
                                 ') ' + ISNULL(OL.LicenseType, '')

                       FROM      ObjectLicenses OL

                      WHERE      O.ObjectID = OL.ObjectID
                      FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                    , 1, 2, ''
                  ) AS 'LicenseType'

  FROM      Objects O

How might I index or otherwise change this model/query to speed it up?


texts: Mariadb times out on startup after system reboot

texts:
This is continued from:

https://stackoverflow.com/q/55071021/8644833

Mariadb shuts down after running for a minute or two. If I remove Mariadb and reinstall (keeping my databases), everything works fine. But if I reboot the system Mariadb exhibits the same issue as before. Which is that it will run for 90 seconds or so and then crash. There are no errors generated from this in /var/log/MySQL, and everything else has the same output as the original post.

All my databases checkout as ok.

I realize I may not be able to provide enough information to get an answer as to how to solve this issue. If that's the case could anyone instruct me on the best way to troubleshoot or reinstall without loosing my data. I have a backup of my WordPress database, but I don't know how to tell if it is part of what's causing the problem.


texts: How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

texts:
I have created table as

      post (
            post_id int(15) auto_increment  primary key ,
            user_id  int(11) ,
            post_text varchar(2000) ,
            post_type int(1),
            attachment int(1)
         ) ;

  Comment (
            comment_id primary key auto_increment ,
            post_id int(11) ,
            user_id int(11),
            comment_text varchar(2000)
         ) ;

and i have three more table to get posts for USER as

friend (id int(11) primary key auto_increment ,user_id int(11) ,friend_id int(11) ) ;
group  (id int(11) primary key auto_increment ,user_id int(11) ,group_id int(11)  ) ;
like   (id int(11) primary key auto_increment ,user_id int(11) ,page_id int(11)   ) ;

I used three table to minimize no of rows in table .

News feed for User "a"

$posts =
    select * from post
    where user_id in
    (
       select f_id  from (
                 (select friend_id as f_id from friend  where user_id = 'a')
               union
                 (select group_id as f_id  from group  where user_id = 'a')
               union
                 (select page_id as f_id   where user_id = 'a'  )
                         )
    )
    order by post_id ,
    limit $start   $end ;

Above query returns maximum 30 posts for user 'a'  ;

 Query for Comment

 foreach($posts as $row){
    $comments[$index]  =
    mysql_query(
          "select * from comment
          where post_id = $row->post_id
          order by comment_id
          limit 0 10 ; "
               );
    $index++;
  }

My problem is How to make it efficient ?

Assume No. of tupples in table as
case 1 : 1 million in post table  and 100 millions in comment table .
case 2 : 10,000 in post table  and 1,00,000 in comment table .

Query is done frequently like social Network site .
Using APACHE server , PHP server side scripting , MYSQL Database .

If it can't be made more efficient than comment .


texts: Prevent Certain Types of Queries (Redshift)

texts:
Some IDEs will allow users to "peak" at data by hitting some button in a menu, and under the hood the IDE performs a select * without a where clause.  This can be a problem because our Redshift cluster includes a table (via Spectrum) which contains a very large number of rows.  Therefore, running select * without a where clause bogs down the cluster unnecessarily.
One option is to train users not to run queries without where clauses on that particular table, but asking users to remember not to hit an innocuous-looking button in their IDE may not work 100% of the time.
Are there other best practices we can explore here?  For example, is there a way to tell Redshift to disallow queries without where clauses on particular tables?  (If for some reason the user really wants all rows, they can include where 1=1 or something.)  Or is there some kind of server-side lint-like plugin, or server-side query regex filter, that would accomplish similar functionality?
Please note that this is not a security-related question.  If a malicious user wants to bog down the cluster intentionally, let's assume they will still be able to do so.  Rather, the intent of this functionality would be to do a favor for users who hit the wrong button or type the wrong thing without realizing the problem.
Thanks in advance.


texts: What are the disadvantages of contained database in ms sql 2017

texts: What are the disadvantages of contained database in Microsoft SQL Server 2017? I want to introduce contained database in my organization. I would like to know what limitations exist. Reasons why I should not implement it would be useful too.


texts:
My DEV instance of SQL Server is super slow for some reason from time to time. It doesn't happen all the time, just sometimes and there are queries that take a very long time. But it's not just that, sometimes just expanding the "Stored Procedures" or the "Tables" folder in the Management Studio takes several seconds. Sometimes doing a simple query also takes several seconds.

I'm working on a small database (~300mb) and in a new laptop with an i7, 8GB of memory and a SSD drive.

Part of the time the SSD goes to 100% when performing slow. However, I don't see a slow down in any of the other applications I use.

I believe the problem is only in one database, but I ran DBCC CHECKDB already and everything was fine. The LOG file is pretty small and both the DATA and the LOG files have space available, so it's not expanding.

It's not just a problem with the Management Studio as the ASP.NET application I'm working on works slow too sometimes. Also, it's not a problem with indexes or anything like that since I'm running a much bigger copy of the database and it works fine (needless to say, such a small database shouldn't have that problem even if it hasn't any indexes).

Any ideas?

Some configurations I've already checked:

Max server memory: 2.5GB
Auto-close/auto-shrink db: False
Auto Create/Update Statistics: True
DB compatilibity level: MSSQL 2008 (100)
Recovery model: Full
Allow snapshot isolation

UPDATE:

I ran Blitz and couldn't find the problem either, these message appear and might or might not be related:

At least one NUMA node is reporting THREAD_RESOURCES_LOW in sys.dm_os_nodes and can no longer create threads.
7 forced grants reported in the DMV sys.dm_exec_query_resource_semaphores, indicating memory pressure has affected query runtimes.

And running BlitzFirst:

THREADPOOL (avg ms per wait): 130832.4
RESOURCE_SEMAPHORE (avg ms per wait): 17202.5


texts: How does Oracle maintain Oracle Client software? I don't find Patch Updates for the Client Software of Oracle.


texts: Blocking drop table for specific schema using trigger

texts:
I'm trying to prevent anyone from dropping tables on a specific schema "public" unless the user is an rds_superuser but the function I wrote is guarding on all schemas.

CREATE OR REPLACE FUNCTION guard_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_TABLE_SCHEMA = 'public' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN
RAISE EXCEPTION 'command % is disabled for this table', tg_tag;
END IF;
END;
$$;

How do I get this to work?


texts: How calculate the time difference between two consecutive rows


texts: Crow's notation: Identifying relationships without the FK in the PK

texts:
I'm trying to wrap my head around the identifying/non-identifying relationships in Crow's Foot notation. I have read in most places that an identifying relationship means that the FK is a part of the entity's PK. However, wouldn't that be the same as simply having the FK be non-null and not include it in the primary key?

For example:

To me this is an identifying relationship, since a Student cannot exist without a University. Though, I do not have plans on including the FK from Student to University in the Student's PK.


texts: Asynchronous availability groups not synchronizing on SQL Server on Ubuntu

texts:
I installed SQL Server 2017 on Ubuntu and setup an availability group with cluster type = "None".
I set the "Availability" Mode to "Synchronous commit" and tested it. It works fine. All transactions to primary are replicated to secondary.

I then changed the "Availability Mode" to "Asynchro" and insert a row on the primary. The row was inserted with success on the primary but did not replicate to the secondary.

I then try to pause and resume the data movement. By doing so, my insert got replicated to the secondary.  I then inserted another row and once again, it did not replicate to secondary.

The Database status on secondary is "Synchronizing".

In the Extended even, I can see that the "Commit_policy_Target" is showing "DoNothing" when I would expect it to show "WaitForHarden".

Has anyone else experienced that strange behavior and found a way to fix it?


texts: script insert SQL Server 2005 from SQL Server 2014 Express

texts:
I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005.

I have generated the scripts to generate the database and to insert the data. All the script generated to be compatible with SQL Server 2005.

I have problems when inserting datetime. I get this error:

  Msg 8115, Level 16, State 8, Line 2
  Arithmetic overflow error converting numeric to data type Numeric.

The datetime exported from SQL Server 2014 is:

CAST(N'2018-10-22 00:00:00.000' AS DateTime)

YYYY-DD-MM works if I edited the query, but it’s a big database

Does anyone have an idea how to solve this?


texts: Why does my execution plan (sometimes) include a left join?

texts:
I found a similar question and am aware that in order for a query of the form

SELECT COUNT(1)
FROM foo f
LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2

to perform without touching bar, there needs to be a unique index on the two columns in question.

And indeed, this works so far, the tables being defined as:

CREATE TABLE [dbo].[Foo](
    [Value] [varchar](255) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Bar](
    [Value] [varchar](1024) NULL,
    [Value2] [varchar](1024) NULL
)

CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[Bar]
(
    [Value] ASC,
    [Value2] ASC
)

The query plan doesn't touch bar, great.

In my quest of why this doesn't work in my real-life query of some app I write, I reduced the query there to the following test that is simple and also has the query planner fails to leave its hands off the bar table:

WITH numbers AS (
    SELECT 1 AS i
    UNION ALL SELECT i + 1
    FROM numbers
    WHERE i < 10
)
SELECT COUNT(1)
FROM numbers n
LEFT OUTER JOIN bar b ON n.i = b.Value AND n.i = b.Value2
;

This does indeed touch bar. Hmmm.

Any ideas? Why does the query planner think this is any different than the other query?

(My actual problem doesn't use this recursive table expression, it's actually just like the demo case, a simple join on two columns - and I can't fathom why it wouldn't leave the joined table alone in a count scenario.)


texts: How can I create a Foreign Key to a Compound Primary Key with a single column?

texts:
I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, but with only a single column, and I am seeking a workaround, no matter how unreasonable (CLR based custom library or dll hijacking, even), just to see how much would need to be done.

Here's an example:

CREATE TABLE dbo.Lookup (
    FieldName VARCHAR(100) NOT NULL,
    Value VARCHAR(100) NOT NULL,
    PRIMARY KEY (FieldName, Value)
)

INSERT INTO Lookup (FieldName, Value)
SELECT 'This', 'A' UNION ALL
SELECT 'This', 'B' UNION ALL
SELECT 'That', 'A'

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100)
)

ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY(Blah) REFERENCES Lookup (Value)

--Cannot create this:
--"There are no primary or candidate keys in the referenced table 'Lookup'
--that match the referencing column list in the foreign key 'FK__OtherTable__Blah'."

This is somewhat expected. I'm acutely aware that the SQL Standard for working with this is that I would need to include another column in dbo.OtherTable that stores FieldName for the foreign key to be created.

However, what if I knew ahead of time that dbo.OtherTable is only ever using FieldName of 'This'? Why can't I then add a foreign key that uses 'This' for FieldName for that check?

I know that I could just add another column, set all their values to 'This', and move on. I'm not interesting in going this route. What I'd like to know is how possible it is to wrangle this into submission. Would it even be possible to write my own foreign-key-like integrity check, should it not be possible to do any other way? Or hijack the built-in Foreign Key implementation?

Basically, all I'm looking for is something like:

ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah]
FOREIGN KEY('This', Blah) REFERENCES Lookup (FieldName, Value)

Is this feature available in other SQL Implementations (MySQL, Postgres, Oracle, etc.)?


texts:
I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table.

So I created a Role (Group) assigning all of the permissions over said database.

Then I created two roles (users) for me and my friend and added them to the Group Role and Inherited all of the Group Role's permissions. So no problem whatsoever.

The problem comes when I create a new table, because the ownership of the table is set to me instead of the group role. Which causes that my friend can't edit the tables I create, and I can't edit the ones he creates.

I did some research and found options like removing the INHERIT flag from my user roles and using a SET ROLE GROUP_ROLE before executing any query. This works but I am looking for a more automated process. We work on Navicat, so we create all our tables visually, fetching the sql, pasting it into the query builder, and adding the SET ROLE line on each query so that ownership is set to GROUP_ROLE makes the process a bit sluggish.

With further research I found out about DEFAULT PRIVILEGES, which I tried but had no success. I didn't understood this option quite well.

EDIT

What I tried was the following commands:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO usergroup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user2;

But still, when user1 creates a new table, user2 doesn't have enough privileges to edit it.

Is there any way that we (me and my friend) can share the same database with ALL the privileges and that if my friend creates a new table I automatically have the permissions to edit it and vice versa?

Is there an instruction so that PostgreSQL sets the ownership automatically to the GROUP_ROLE instead of the user executing the query?

Or maybe changing the default privileges so that when my_user creates a new table, all of the users under my_group share the same permissions?

PD. I'm running Ubuntu 14. And when I was trying to do the DEFAULT PRIVILEGES configuration I was using the default user I guess.

I just log in to my server via SSH and do the following,

sudo su postgres
psql
/c my_database
ALTER DEFAULT PRIVILEGES .... ... ... ..

Thanks,


texts:
I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run.
for eg: i have a a 2 step job and the second step must run and the first step has to be disabled or stopped along with the other jobs.

First of all i thought of stopping the sql server agent, but that will stop all the jobs. Is there any way to do it.


texts: Explain plan gives different results depending on schema

texts:
I have a query that joins a multi million row table to one containing hundreds of thousands of rows.
Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both have select, insert and delete privileges on each of the tables however 'DEF' also has SELECT CATALOG role.

There are no synonyms pointing either user to different versions of the same table.

When performing an EXPLAIN plan from user 'ABC' we see NESTED LOOP as the join mechanism and the query just runs and runs. the same query when EXPLAINed from the 'DEF' user (the one with SELECT CATALOG) shows a HASH join. It seems that the HASH join is the more efficient in this case but that is largely irrelevant for the question.

My question is why would the optimizer choose different joins for the same query depending on which user is running the query ?


texts: T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location

texts:
I am using the following query answered by Marcello Miorelli. This query serves the purpose for normal backups but fails to determine the actual backup size of all the split backups and copy only backups. Ideally, I want to have the list of all the database backup information (Size, Last backup date, location) including split and copy-Only backups.
Can anyone help me tweak this bug to fetch the accurate database backup size.


texts: SQL Server Agent Invokes Failed 'sa' Login

texts:
I recently upgraded to SQL Server 2016. I was looking through the SQL Server logs and noticed the following error.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]

I have figured out that the running a transaction log backup triggers the error. The weird thing is that the maintenance job is successful. I can't figure out how the job can be successful with the failed login attempts. Another thing is that every time the job is ran, it logs exactly 30 failed attempts. What is failing to log in during the execution of the transaction log backup and how do you fix it?

Any ideas or solutions would be appreciated. Thanks


texts: Replicate update statement as Insert in Mysql 5.6

texts:
I want to replicate all update statement from Master as a Insert at Slave end.

Is it possible like in SQL Server? If so, how?

I want to do it because, if any record on Master is updating and if same record is not present at Slave end, then instead of Replication tends to fail, it should insert that record at Slave end.

All insert on Master will act as Insert only at Slave end.


texts:
I am wondering if someone could help me out with a question I have about time stamp based concurrency.

I found the following practice problem on a website

It basically asks which of the following 4 will occur for the LAST transaction:

(a) the request is accepted.

(b) the request is ignored.

(c) the transaction is delayed.

(d) the transaction is rolled back.

The  stands for the order of the time stamps and the  = read  = write

st1; st2; r2(A); co2; r1(A); w1(A)

The solution they give is choice (d) which had me kind of confused as to how they are coming to that.  I may be mistaken about how the time stamps work but I thought that if a transaction wishes to access a shared variable it must have the earliest time stamp.  In this example transaction 2 tries to read(A) first but I thought that should be delayed (since transaction 1 has the earlier of the 2 time stamps.  So once we get to the r1(A), w1(A) since transaction 1 has the earlier stamp that should succeed as I understand it so I'm not sure why they choose (d).  If I am misunderstanding the concept could someone please explain it to me I would greatly appreciate it.


texts: Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS

texts:
I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1.

The restore seems successful, but the output gave me 27 ignored errors. Here is the output: http://developers.mapseed.org/posts/restore-output.txt

Should I be concerned? All of my unit tests are passing, but this is a production database, so I want to be sure. They seem to be errors related to permissions, but both my pg_dump and pg_restore commands were run with the postgres user. Although perhaps I'm missing something.

step by step of the dump/restore:

My database is named shareabouts_v2, so on my RDS instance, I ran a create database shareabouts_v2 and create extension postgis.

Then I created a dump file from my current database with the following command:

pg_dump -f backup.dump -Fc -p 5432 -U postgres -h localhost shareabouts_v2

and I restored that dump file into my RDS instance with the following command:

pg_restore -v -U postgres -h <my-RDS-host> -p 5432 -d shareabouts_v2 --disable-triggers -e backup.dump

I have followed numerous posts and best practices around dumping and restoring PostGIS data

Update

These errors were also encountered on this SO answer: https://stackoverflow.com/a/22247401/1884158

UPDATE

I was able to "resolve" the errors with the function declarations by removing them from the dump file, as described here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192

where I basically did this:

pg_restore -l -f out.txt backup.dump
# then edit out.txt to delete all of the function definitions and spatial_ref_system:
pg_restore -L out.txt -U my_user -h my_host -p 5432 -d my_db -e --single-transaction backup.dump

But I'm not sure if that will result in other issues. I'll post more updates as I continue...


texts:
We're planning to sell a small ERP product that utilized PostgreSQL on the backend, targeting between 10 to 500 users per client. The management insisted on getting paid support for PostgreSQL, but the quote we got from various vendors around our region is not competitive or even prohibitive and more expensive that what we've been paying Microsoft for their support locally. We've been reliant on Microsoft products all these time. Only recently we started looking into alternatives.

I'm more supportive of using OSS and I've since pitched that we could develop the skill in house and support it on our own. Few months back, I've helped to transition a small team from TFS to Gitlab(PostgreSQL backend) and I've not faced any show stopping issues. I'm confident that PostgreSQL is matured and
we don't really need paid support like we do with Microsoft/Oracle products.

But so far I don't have a strong argument. I would like to gather more information for my argument and also to understand what's norm when using OSS for enterprise application. Above how many users should one subscribe to paid support?


texts:
I'm a bcp newbie and I want to insert a large file. After bunch of errors I made the following toy example, that I'm trying to use in order to build a baseline that I can build on.

Table SQL:

"Create Table dbo.my_test ( " + \
        "transaction_id BIGINT ," + \
        "transaction_no INT ," + \
        "store_no INT ," + \
        ");"

my csv file

771666690,346138,400

my format file:

13.0
3
1       SQLBIGINT           1       8       ","   1     transaction_id                                                       ""
2       SQLINT              1       4       ","   2     transaction_no                                                       ""
3       SQLINT              1       4       "\r\n"   3     store_no                                                             ""

my bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

command line output:

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Not sure what to do?


texts: SQL Server Agent fails after installing SQL Server 2019 CU2

texts:
After installing CU2 our SQL server agent randomly stops working.  If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click:
  https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

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

  A severe error occurred on the current command.  The results, if any,
  should be discarded. (Microsoft SQL Server, Error: 0)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4013&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

Any clue what could be causing this?


texts:
Context:

I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt.

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

Now that I am equiped with the XML File containing all the information that I need, I would like to load this into another database.

Since it is that easy to create this table dump into a xml file, I suppose there must be a simple way to load the XML File into a database (Creates the Table and then loads the data). Does anyone know how to do this?

Disclaimer: I am not a DBA and this is my first experience with BCP, the documentation was talking about creating a Format file but it didn't seem to work for me.

Thanks in advance for your responses


texts: Need help with query over three related tables

texts:
I am designing a database for a publishing house to track magazine shipping and also to keep a history of it.

I have three tables (simplified):

Subscriber
  id (pk)
  subscribers_number (unique)
  first_name
  last_name
  shipping_address
  last_payment

Shipping
  id (pk)
  date
  issue

ShippingAddress
  id (pk)
  shipping_id (fk)
  subscriber_id (fk)
  subscribers_number
  first_name
  last_name
  shipping_address

NonDeliveryReport
  id (pk)
  shipping_address_id (fk)
  reason
  resolved

Subscriber is a list of people who pay to receive a monthly issue of their magazine. Not every subscriber is eligible to receive the next issue. To make it simple, a subscriber receives the next issue only if last_payment date is withing the last 60 days. There are some more conditions, but I'll get into that later.

When the next issue is ready to be shipped, a Shipping is created.

Then we select all eligible Subscribers and create a ShippingAddress for each one and copy the value of subscribers_number, first_name, last_name and shipping_address from the Subscriber to the new ShippingAddress. We do this so that we have an unmutable history of shipments, even if a Subscriber changes his/her address, or is deleted from the database. For ease of use, there is a nullable foreign key subscriber_id in addition to the hard copy of the subscribers_number.

If the mail company could not deliver to a subsriber's ShippingAddress, they return a non-delivery report to us. For each report, a NonDeliveryReport is created and references the affected ShippingAddress.

Now we are getting to the important part of my question:

When we create a Shipping, we have to select all Subscribers, that are eligible. Eligible is every subscriber, except for anyone, who has a non-delivery report attached to him, that was NOT resolved. (For simplicity's sake, we just ignore all other conditions.)

So I need two queries:

Select all subscribes that are eligible.
Select all subscribers that are not eligible.

Query #2 was managable and rather easy:

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
  INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.resolved IS NULL;

But the queries I wrote for case #1 always returned fewer or more results than expected. I am stuck on this since last week and just can't get it to work. I hope you could help me with that.

This isn't part of my main question, but any thoughs (in form of comments pls) about my database design are welcome. I was already thinking about adding a field deliverable (bool) to Subscriber and just fill in the value programmatically, when importing the NDRs, but am hesitant because that'd probably defeat the purpose of database normalization.

Edit #1: The closes I came to was the following query, but it returns too many rows. *sigh*

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.Subscriber_id)
  LEFT JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.ShippingAddress_id)
WHERE NonDeliveryReport.korrigiert_am IS NOT NULL OR NonDeliveryReport.id IS NULL OR ShippingAddress.id IS NULL

Edit #2: After thinking even harder (I didn't think that was possible any more) I found a solution.

When non-eligible are subscribers that have an open NDR (resolved IS NULL), then eligible is anyone who as no NDR in addition to anyone who has more than 0 unresolved NDR.

-- List of ELIGIBLE Subscribers
SELECT DISTINCT Subscriber.id
FROM Subscriber
LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN
  (
    SELECT ShippingAddress.subscriber_id
    FROM ShippingAddress
    INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id
    WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL)
    GROUP BY ShippingAddress.subscriber_id
  )
ORDER BY Subscriber.id;


texts: Index to facilitate joining a table against itself to gather the last observation

texts:
I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as possible in this table so that we can construct month-to-month change reports that go as far back as possible.

for example, the data looks similar to this:

| p_key | monthend_id | val |
|-------|-------------|-----|
| 0     | 8/31/2018   | 100 |
| 0     | 7/31/2018   | 90  |
| 0     | 6/30/2018   | 110 |
| 1     | 8/31/2018   | 25  |
| 1     | 7/31/2018   | 31  |
| 1     | 6/30/2018   | 22  |

To do this, I need to match the table to itself where the two are exactly 1 month apart. Right now I have a query that runs with a join like this:

SELECT *
FROM monthend_table t1 LEFT JOIN monthend_table t2
ON t1.p_id = t2.p_id
AND to_char(add_months(t1.monthend_source, -1), 'YYYY/MM') =
to_char(t2.monthend_source, 'YYYY/MM')

This works fine, and I may even be able to take out the character formatting, however I notice in my execution plan that the index on monthend_source isn't being used, and so it's doing 2 full scans.

How would I best construct indexes that would facilitate the fastest join possible?

Should I create one for (p_id, to_char(monthend_source, 'YYYY/MM')) and another for (pid, to_char(add_months(monthend_source, -1), 'YYYY/MM'))? Is a single index better?

I often find I struggle to know when and how to create effective indices in (what is to me) more complicated situations like these, so any advice would be greatly appreciated!


texts:
How Can I make the order by based on what I input on where?

example query

select * from student where stud_id in (
'5',
'3',
'4'
)

the result would be

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

Kindly help me thanks.


texts: Huge database... queries too slow. How to improve performance?

texts:
I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array of integers as text(varchar couldn't become large enough).

A simple

  SELECT row FROM tablename LIMIT 200,000;

takes 30 seconds to complete... I need to select all 30 million rows and go through some algorithms in at most 10 seconds to be ideal.

I'm not used to managing such a large database. Is it normal for a queries to take this long? I've heard about indexing, but can't index the text data type column.

What are some ways you suggest I can improve performance? I can't find too many ways to improve performance other than:

Indexes
More RAM
SSD
Partitioning tables(sound complicated?)

Is there anything I'm not thinking of?

I appreciate any help! :)


texts: Install SQL Server Express r2 2008 using a configuration file

texts:
I need to deploy my application with SQL Server Express. There is a way of installing sql express using a configuration file so that when deploying my application it appears as if I am only installing one thing and make it easier for the user installing the software. Anyways here are the steps:

Step 1)

Create the configuration file:

Step 2)

Place the Setup.exe (SQL Server 2008 R2 Express)  file next to the configuration file. I place both files at the root of my c drive for simplicity

Step 3)

Execute the setup.exe passing the parameters:

I been getting a lot of errors. Can someone show me how can I have a configuration file in order to install sql server express edition with the administrator as the main user.I don't want the user installing the application to have to go throughout that trouble. I found a nice link that teaches you how to create a configuration file in here but I still am not able to do it. I am using windows xp 32 bit. I managed to installed it on the virtual machine manually. I will like to be able to install sql server express from the command line or with a configuration file.


texts:
So we have this software that queries for content of Catalogue:

10:55:36,193 [ INFO] [rest.CatalogProvider:232] GET_CATALOG: {
  Id: '*',
  Title: '*',
  Clip: '*',
  IniDate: '*',
  EndDate: '*',
  DurMin: '*',
  DurMax: '*',
  Store: '*',
  FilterConfig: '*',
  FilterNoStore: '*',
  InfoStore: '*',
  Channel: '*',
  Group: '*',
  Type: '*',
  Validated: '*',
  SourceType: '*',
  NumResults: '*',
  StartIndex: '*',
  Order: '*'
}

This terminates in error:

10:55:36,285 [ WARN] [util.JDBCExceptionReporter:233] SQL Error: 207, SQLState: S0001
10:55:36,285 [ERROR] [util.JDBCExceptionReporter:234] Invalid column name 'Locked'.
10:55:36,286 [ERROR] [service.CatalogService:434] getCatalog Exception could not execute query

However I am quite sure we do not have a column name Locked. Where is this 'locked' coming from?

The server is SQL Server 2008.


texts:
In postgresql db I have a 'data' column which type is json. It looks like following:

{"columns":[{"name":"cusno","displayName":"Customer number","type":"text"},{"name":"cname","displayName":"Name","type":"text"},{"name":"cadd1","displayName":"Address","type":"text"},{"name":"cstte","displayName":"State","type":"text"}],"variables":[{"name":"var1", "type":"text"}, {"name": "var2","type":"text"}]}

I need to do search for example when user types 'cu' it should return rows which columns name or display name contains this word or variables name contains this word. How should i write the where clause ?


texts: Why choose a top query and temporary table instead of a cursor for a loop?

texts:
When it comes to looping over a large set of data in T-SQL, the majority of examples I see on the Internet use a pattern like this:

declare @someVariable int
declare @remainingRows int

select someColumn from someTables into #someTempTable

select @remainingRows = count(*) from #someTempTable
while @remainingRows > 0 begin

    select top 1 @someVariable = someColumn from #someTempTable

    -- Do stuff

    delete top 1 from #someTempTable

    select @remainingRows = count(*) from #someTempTable
end

This seems to be way more common than samples of using cursors like this:

declare @someVariable int

select someColumn from someTables into #someTempTable

declare @someCursor cursor for select someColumn from #someTempTable
open @someCursor
fetch next @someVariable from @someCursor

while @@fetch_status = 0 begin

    -- Do stuff

    fetch next @someVariable from @someCursor
end

close @someCursor

I sometimes hear mentioning of performance reasons, but to me that's counter-intuitive; wouldn't a forward-only read-only cursor iterating over a set of data be way more efficient than constantly querying and updating a temporary table? I understand that if it was a cursor iterating over a table with the wrong options that the table could be locked for updates, but a cursor over a non-shared temporary table wouldn't have that problem, would it?

Bonus question; if cursors are OK, would a cursor over a regular query that uses snapshot isolation be safe so I can avoid the annoyance of manually creating a temporary table, i.e.:

DECLARE @someCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT something
FROM tables


texts: Strategy for backup for databases with little use (almost archived databases)

texts:
SYSTEM:
I have a system with about 150 databases, each of about 2gb in size.

130 of those are mostly inactive but need to be online. Those are set to simple recovery, active dbs are set to full.

I have a daily full backup and hourly tran log backups. I am using sql server maintenance plan backup and I am backing up all user databases

PROBLEM
This (the full backup) generates a large backup set that, for various reasons I am finding it hard to deal with.

QUESTIONS
I am looking for a back strategy that would not result in unnecessary backup size.

I am not using differential backup (which I think would reduce the size of the backup of the inactive DBs) because I am afraid of having to deal with large set of files in case of having to rebuild from backup.

I know I could maintain different backups maintenance plans, and update it as a db goes inactive, and as I add new active databases, however, this is manual process and we add a few databases a month. I've done this before and mistakes were made...

Is there a way to take advantage of the fact that inactives are simple recovery to threat them differently?

How about the backup compression? I have never used it, not sure what this will do to my CPU usage (currently the DB server is typically <15% CPU)

Thank you!

PS, i am not a DBA, but a developer.


texts: Procedure to wholly migrate SQL Server 2005 to a new server

texts:
I'm tasked with 'rebuilding' a database server, because the old one is dying. To reduce potential for the new server f**king up, we had decided to rebuild the server as similar as possible to the old one, hence, SQL Server 2005 Enterprise on top of Windows Server 2003.

Now, the original dev/maintainer of the old server has gone the way of the dodo (as in, he's left the company quite some time ago, and nowhere to be found now). So, we had to do the migration in-whole.

To make matters worse, the old server is a hodge-podge of: Databases, a mix of user credentials (SQL Server authentication and Windows authentication), a crowd of Linked Servers, and a gaggle of SQL Server Agent Jobs.

So, how do I approach this monumental task? In my mind, I had the following half-baked plan:

Copy the databases using the Copy Database Wizard
Copy the users using Script > CREATE To
Re-create all Linked Servers
Copy the Jobs using Script > CREATE To

For every step above, I might have to do it one-by-fricking-one :(

Is there an easier (as in, less tedious) way to do the migration?


texts:
apt-get install postgresql-10-repmgr  give me repmgr 4.3.  However, my primary server is still on 4.2.

How do I install repmgr version 4.2?

Thanks in advance!


texts: MySQL replication constantly checking tables

texts:
I recently moved one of our slave databases to a new server, the database is purely used for offsite replication/backup and doesn't handle any of the live traffic.

Since moving, the new server seems to constantly want to run CHECK TABLE across the whole database. We've got a a few large tables, one being ~10GB, so it locks everything up for a few minutes while it's checking the table and in result queues up the queries.

I've looked at possible causes as to why it needs to check so often, but can't seem to find a reason.

I was wondering if anyone here has experienced this, or would know of a reason?


texts:
I am currently doing high insertions into my MariaDB 10.4.12 database via scripts.
However, sometimes the insertion queries just get stuck/piled up at what it seems to be at the Update or Commit state.

The above insertions that I am mostly doing (95% of the time) are INSERT IGNORE INTO table1 ('b1','c1','a1') VALUES (?,?,?)
Sometimes, they can even go up to 300+ seconds, until eventually all the insertions queries slowly get cleared up within 5-10 seconds (it seems like something is stuck)
I have thought of few things to improve the insert operations :

Do BULK insertions instead of many single insertions
Use transactions
Increase innodb_log_file_size

However, ontop of doing that, I would like to know if this is due to other reasons.
When I checked my iotop , half of the time it shows that mysqld is having 99.99% I/O
I am not sure if the disks are failing (2TB x4 in mirror setup) or is there some configuration settings that I did not tuned properly?
Table schema :
table1 (
  a1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  b1 char(64) DEFAULT NULL,
  c1 bigint(20) unsigned DEFAULT NULL,
  d1 datetime DEFAULT NULL,
  e1 tinyint(1) DEFAULT NULL,
  f1 int(11) DEFAULT NULL,
  LastUpdate timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (a1),
  UNIQUE KEY idxc1b1 (c1,b1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

my.cnf :
[mysqld]
symbolic-links=0

innodb_thread_concurrency=32
innodb_buffer_pool_size=240G
innodb_buffer_pool_instances=64
innodb_page_cleaners=32
innodb_purge_threads=1
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_autoinc_lock_mode=2
innodb_file_per_table=1

max_connections=10000
skip_name_resolve=1
tmp_table_size=134217728
max_heap_table_size=134217728
back_log=1000
wait_timeout=900
innodb_log_buffer_size=32M
innodb_log_file_size=768M
open_files_limit=1024000
max_allowed_packet=512M

Update #1 : Added SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS during queries piled up (stuck for 120s+)
GLOBAL STATUS : https://justpaste.it/3hxey
INNODB STATUS : https://justpaste.it/5uren
Update #2 : Added SHOW VARIABLES
SHOW VARIABLES : https://justpaste.it/3chha
Update #3 : After replacing the disks (which are also SSDs), the issue is solved. This means that choosing the right SSD disk model is very important for a database high insertion/write rate


texts: Mongo E11000 duplicate key error collection after mongo 3.4 to 3.6 upgrade

texts:
I have a Mongo replica set on Ubuntu with three members configured for my information system and am having issues when I try to upgrade MongoDB from version 3.4 to 3.6

After setting the "featureCompatibilityVersion" to 3.4 on the master node and checking it on the two slaves, I started upgrading the slaves one by one (I gracefully shut the DB down with db.shutdownServer(), update the packages using apt and start the mongod service). That all went fine and the slaves transitioned back to secondary states. I then shut the master down, repeated all the steps for the upgrade from the official guide (https://docs.mongodb.com/manual/release-notes/3.6-upgrade-replica-set/) and tried starting the mongod service again.

However, while I was upgrading the master node, the slave that was elected as a new master crashed with the error:

  [rsSync] Fatal assertion 40454 DuplicateKey: E11000 duplicate key error collection: admin.system.users index: user_1_db_1 dup key: { : "john", : "admin" } at src/mongo/db/system_index.cpp 134

I didn't notice this and when the old master started up again it too crashed with the same error. In my frantic trying to get the members up again, the third member had also been elected as a master for a short time before crashing with the same error.

When I managed to get the first member up again as a standalone, I checked the system.users collection and found that there were indeed two users with the same username for the admin database (I redacted the parts with ...):

{ "_id" : ObjectId("5...8"), "user" : "john", "db" : "admin", "credentials" : { "MONGODB-CR" : "f...c" }, "roles" : [ { "role" : "read", "db" : "admin" } ] }

and

{ "_id" : "admin.john", "user" : "john", "db" : "admin", "credentials" : { "MONGODB-CR" : "f...c" }, "roles" : [ { "role" : "read", "db" : "admin" } ] }

My question is how is that possible? Shouldn't mongo prevent someone from adding an identical user? Or did mongo try to change that collection after the upgrade and failed? I am using authSchema version 3 if it makes any difference. Thanks!


texts: Temp postgres table ignores "excluding constraints"

texts:
In PostgreSQL 9.5.

I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } - which I'm attempting to use.

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    primary key (col1)
);

create temporary table t (like test excluding constraints);

insert into t (col1, col2, col3) values (1, 2, 'foo'), (null, 2, 'bar');

If I'm understanding excluding constraints correctly, that should not bring over the non-null constraint on test to t.

But here's the error that I receive:

ERROR:  null value in column "col1" violates not-null constraint
DETAIL:  Failing row contains (null, 2, bar).
********** Error **********

ERROR: null value in column "col1" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, 2, bar).

I have a workaround, create temporary table t as select * from test where true = false, but I'd like to know what I'm not understanding correctly about excluding constraints.


texts: How do I terminate the old connection when a user of the same account login again?

texts:
I want to set up a server while multiple users could login at the same time, as long as they are not the "same user".
How could I set it up so when the user with the account username "Bob" login again, whether it's from the same computer or another, the old connection will be terminated?
Thanks for your help.
Much appreciated!
Edit:
"Bob" is a Database user account, the "User ID" in the "Connection String", I'm sorry I don't really know what it is called.


texts: An event has run for certain amount of time. How to find the time for how long this event has run in mysql?

texts:
I am using MySQL. I ran one event it took long time.
How to find the time for how long this event has run?


texts: Extent Allocation Mechanism in SQL Server 2017

texts:
I was looking at the SQL Server 2017 Documentation for page and extent allocations.

What I got to know from online resources and sessions in SQL conferences: Starting with SQL Server 2016, Uniform Extent Allocation is the default behavior. However, what MS documentation describes is:

  To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data.

  Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
  Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

  A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations

So, my question is: In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?


texts: Can a table column be written to treat two different values as the same value?

texts:
The title is a bit generic, but the specific issue is that the United Kingdom of Great Britain and Northern Ireland has two commonly used 2-character codes. ISO 3166-1 specifies the code GB as the proper code, but many people still use UK for that country.

Creating a separate table to store alternate country codes and updating all my queries against the main table to also perform a secondary lookup on the alternate codes table seems like overkill for a single country. At this time, I only know of that one country where this may be a problem - at least with Alpha-2 country codes.

So what I'm wondering, is if there is a way to construct a table or column such that querying a single nchar(2) column with either UK or GB will return the same record?

I know how to write individual queries to do this, but that means updating many queries in many places (stored procedures, views, UDFs, and even application code outside the database). I'm almost wondering if I can code a custom collation, inheriting from an existing collation and just add something to make the two character sequences equal to each other..?


texts: Taking load off the Master server by using a slave

texts:
My gameserver utilizes a Master and a slave on one server. The reason I am wanting to use a slave is because I want to beable to run select queries (read only) to get data without affecting the Master database.

For example, a table on the Master gameserver that is being replicated is called rpg_user_items. It stores hundreds of thousands of items, and I want to select some of this data to show to the player (without being in-game); think of a web inventory browser.

My dilemma is: Would selecting the data from the slave be the more appropriate thing to do? And would it technically take load off the Master server? (Maybe, the Master server can even perform better since select queries are not being used on it all the time?)

Edit: My MYSQL version is 5.6.24, using InnoDB. The frequency of insertions are around 20+ req/s. (Inserting 1 row into rpg_user_items) with 15 row fields being a mix smallint and tinyint data types.


texts: What does Page Life Expectancy say about the instance?

texts:
I have installed monitoring software on a few SQL Server instances in the environment. I am trying to find bottlenecks and fix some performance issues. I want to find out if some servers need more memory.

I am interested in one counter: page life expectancy. It looks different on every machine. Why does it change often on some instances and what does it mean?

Please take a look at the data from the last week gathered on a few different machines. What can you say about each instance?

Heavily used production instance (1):

Moderately used production istance (2)

Rarely used test instance (3)

Heavily used production instance (4)

Moderately used test instance (5)

Heavily used data warehouse (6)

EDIT: Im adding the output of
    SELECT @@VERSION
for all of these servers:

Instance 1: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 2: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 3: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 4: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 5: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 6: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

I also ran the following query on the machines:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

and it returned 2 or 3 rows for each server:

Instance 1: 0; 64; 1
Instance 2: 0; 64
Instance 3: 0; 64
Instance 4: 0; 64
Instance 5: 0; 64
Instance 6: 0; 64; 1

What does it mean? Do these servers run NUMA?


texts: What is most efficient way to store one or two chars?

texts:
In Postgres table with ca million rows I have a column bookingtype (character varying(20)), which default value is null and besides that it may have 7 distinct values (1-2 chars). Current situation:
 count  | bookingtype
--------+-------------
 389408 | w
 143780 | [null]
  18240 | i
  29496 | a
 356323 | m
    959 | e
   7936 | sl
     55 | kr
(8 rows)

I have btree index for the column, but I am not sure, is it optimal for such situation, where is so few distinct values.
I need to filter rows based on the value of bookingtype and it is always equality filtering I use.
Does special enum-type with 7 possible values have advantage before varchar? Does it need indexing?
What is the best way to implement such column with few values in many rows?
Postgres 9.6


texts: How to verify if a SQL Account is still used?

texts: We have a SQL Server Login on a SQL Server 2008, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server connection, however no one knows if it is still really being used, and if so for what. How do we verify, aside from disabling and waiting for a squeal?


texts: Insert string content into a blob in DB2 database using a simple SQL query

texts:
I am having trouble inserting in a BLOB field some text content (in fact it's an XML, I know there are field-types more suited than BLOB for this type of content, but I simply can't change it).

I have the necessity of inserting some values using simple SQL queries, I am using this syntax:

INSERT INTO XXX.MYTABLE (MY_KEY, MY_VALUE)
 VALUES(0, CAST('<some xml content here/>' AS BLOB) );

The command is executed, but it stores in the database some application/octet-stream content that converted back to text appears like

  LÓ…££…™?çÔÓ£…¢£–n%L£‰—–É•„‰™‰©©–nÁLa£‰—–É•„‰™‰©©–n%L¢?“¤£‰nâ—…££K“…La¢?“¤£‰n%L–‡‡…££–nÓ…££…

I guess it is an encoding issue, but the documentation says "BLOB does not have  any Coded Character Set Identifier (CCSID) associated with the field", so I do not know how to work around this issue.

The DBMS is DB2 10.1


texts: Re-order columns values based on values in a table

texts:
I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data.

Suppose the table contains the data as below with no primary key/unique key constraints:

Col1    Col2    Col3     Col4
----    ----    ----     ----
B        D       C        A
C        Y       M        T
3        5       2        4
5        2       10       7

I want the output in below format: Each row data should be in ascending order, Whether it's Varchar or Numeric. That I guess could be done by taking ascii value of the value set? Is it achievable, if not why?

ColA    ColB    ColC     ColD
----    ----    ----     ----
A        B       C        D
C        M       T        Y
2        3       4        5
2        5       7        10

Please try to provide a solution, which can be generic for any number of columns.

Every row should be re-arranged in ascending order, while we are retrieving the data. I am trying to do something like order by, but not based on column, but row. Is it achievable. It was an interview question for me by Amazon. I tried pivot and unpivot, but haven't been able to achieve this.

More information:

All column datatypes are VARCHAR2.
No specific order/pattern for the values.
The column names does not need to be same, while retrieving the data. Column names can be aliased or can be taken as per your preference. The data retrieved should be in the ascending order by row.
No primary key/unique key constraints.

I would like to understand, if such a solution is achievable, whether inefficient or not. Looking for answers using Oracle 11g or higher.


texts: Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

texts:
I am stuck at this for a while and had been Googling for answer but can't seem to find any....
I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the LINUX server, there are thousand of rows so I need to write a kind of loop to accomplish this.

I got this :

DECLARE
    i    NUMBER := 0;
    l_file      UTL_FILE.FILE_TYPE;
    l_buffer    RAW(32767);
    l_amount    BINARY_INTEGER := 32767;
    l_pos       INTEGER := 1;
    l_blob      BLOB;
    l_blob_len  INTEGER;
BEGIN
    FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname like '%png')
    LOOP

            i := i + 1;
            --DBMS_OUTPUT.put_line ('Record ' || i || ' id ' || rec.id);
            --DBMS_OUTPUT.put_line ('Record ' || i || ' fname ' || rec.fname);

        l_blob_len := DBMS_LOB.getlength(rec.fblob);

          -- Open dest file.
            l_file := UTL_FILE.fopen('BLOBDIR',rec.fname,'w', 32767);

          -- Read chunks of the BLOB and throw them to the file until done.
        WHILE l_pos < l_blob_len LOOP
                DBMS_LOB.read(rec.ATTACHMENT_BLOB, l_amount, l_pos, l_buffer);
                UTL_FILE.put_raw(l_file, l_buffer, TRUE);
                l_pos := l_pos + l_amount;
            END LOOP;
         END LOOP;

      -- DBMS_OUTPUT.put_line ('Procedure Looping Example is done');
   END;

I tested the Loop with the DBMS_OUTPUT and it worked fine. However:

When I ran the code all 10 of the *.png files show up in my BLOBDIR - but the files is empty.
When I ran the code for each individual file (i.e.  FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname ='filename.png')) - I got the file and it has data.
When I ran the code with only 2 files in the loop (i.e. FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname in ('filename.png', 'filename2.png')) - both files got written to the BLOBDIR - but only the first file has data, but the second file show 0 data.

I ran this code through TOAD, and through sqlplus - and same result.  Can someone help me with this ?
Thank you.


texts: How to check which users have been created by specific stored procedure in sql server?

texts:
I have created a stored procedure, which creates different username and password.But i have condition that for next time when stored procedure is called, it should delete previous user created by same stored procedure.And we are not supposed to store user details in table.
So could anyone can suggest me how to know those users which are created by only this stored  procedure?
Thanks.


texts: Date pattern regex doesn't match the records in PostgreSQL query?

texts:
I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined for finding any kind of date pattern!, so, I have created a few using some examples found in the database such as:

9/18/2010 6:17:00 AM
 05/21/2014
Date 2008-02-13
7/18/2016 9:54 PM
 2000-04-20
Booked:1/16/2011 6:45:00 AM (2008-09-01 12:35:45 )
on January 3, 2008
on July 3, 2002
23 Mar 2017
5 Feb 2004
 January 3, 2008
January 31, 2001
December 19, 2007
February 2009
February 30
- Feb 3, 2011
9 Jun 2010

This REGEX matches a few date formats and is able to find the records in the database!:

select count(*) from gallery_image where description ~* '(\d+)\/(\d+)\/(\d+)|(\d+)-(\d+)-(\d+)';

But, this REGEX matches the example, but unable to find a single record in the database, even though they are present in the DB since I have taken all the examples from it.?

select count(*) from gallery_image where description ~* '(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})';

Could anyone point out the mistake I have made in the second query or REGEX pattern that it works normally but not when used in the PostgreSQL query?


texts:
If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time?

CREATE PROCEDURE [dbo].[PayoutInvestment]
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END;

    COMMIT TRANSACTION;
END

I know it works 100% if I do it with updlock hint. But would like to know if it is necessary to do it that way.

CREATE PROCEDURE [dbo].[PayoutInvestment]
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments WITH (
                    ROWLOCK
                    ,UPDLOCK
                    )
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END

    COMMIT TRANSACTION;
END

I am using Read Committed isolation level.


texts: Mysql 5.7 / Ubuntu14.04.5 LTS in-place upgrade - Package 'mysql-server-5.7' has no installation candidate

texts:
I have tried upgrading in-place to Mysql 5.7.x from 5.6.33 as well as directly from 5.5.46 on Ubuntu 14.04.5 LTS, but I keep getting stuck at the same step.

I followed this answer successfully to upgrade from 5.5.46 -> 5.6.33 in-place: https://askubuntu.com/a/750502/482945 but I could not get it to work for 5.5.46 -> 5.7.x .

After installing mysql-apt-config:
$ sudo dpkg -i mysql-apt-config_0.8.0-1_all.deb

I tried this several times:
$ sudo dpkg-reconfigure mysql-apt-config

Each time on the displayed menu (see https://askubuntu.com/a/750502/482945) I selected the 5.7 option followed by:
$ sudo apt-get update
$ sudo apt-get upgrade
$ sudo apt-get install mysql-server-5.7

But I get the same message:

Package mysql-server-5.7 is a virtual package provided by:
percona-xtradb-cluster-server-5.7 5.7.16-27.19-1.trusty
percona-server-server-5.7 5.7.17-11-1.trusty
You should explicitly select one to install.

Package 'mysql-server-5.7' has no installation candidate

I verified that the repo actually contains a 5.7 binary: http://repo.mysql.com/apt/ubuntu/dists/trusty/mysql-5.7/binary-amd64/Packages

$ apt-cache search mysql-server-5.7
also does not show mysql-server-5.7

$ sudo apt-get update hits 5.7 urls, but does not fetch the packages.

In one attempt mysql-common was "held back" by apt and probably that was causing the absence of 5.7 but I ran apt-get upgrade and removed mysql completely, but still no use.

As mentioned above, I've tried 5.5 -> 5.7 as well as 5.5 -> 5.6 -> 5.7 but I simply do not get the package to install.

This blocks my attempt to run an in-place upgrade test.
A logical upgrade will need significant downtime which I cannot afford.

Any help is greatly appreciated !

PS: There are other unrelated "gpg key expired" errors because MySQL Release Team's GPG keys(1487236823) and/or Percona's APT keys(8507EFA5) have expired or changed - these errors are easily fixed with some googling.


texts:
I am using mysql as my backend database. Here is the use case;

A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. Each offering belongs to different types of variants:

Hotels
Origin city

For starters, I want to design the use case of Origin city:

There is a GOA package. It can be offered in 3 variants based on Origin city;

From New Delhi to Goa
From Bombay to Goa
Goa itself ( Land package )

(Bold implies default selection)

a. and b. User can fly from either  New Delhi or Bombay to GOA.
c. Goa is a Land package, where a user can reach Goa by himself and is a standalone holidayPackage.

Also, we need to further support the following use cases with a variant category (Origin City):

one of the options in a variant can be marked as default by the admin, which will be a part of the offered inclusions (e.g. Bombay  to Goa)

In the database, I need to model these relationships. Here is what I have in mind.

HolidayPackage has MANY to MANY relationship with Destination (read origin city). This will take care of options like:

From New Delhi to Goa
From Bombay to Goa

My concern:

How do I handle Land packages only i.e. "Goa itself" and we don't have a travel leg ?
How can I mark an option for a package, default at the database level ?


texts:
Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment.
Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command 'CHECKPOINT', operation 'FlushCache', scanned buffers 144321190, total iterated buffers 150079215, wait time 42 ms. See 'https://go.microsoft.com/fwlink/?linkid=2132602' for more information"
This is a new feature that is included in the 2017 CU23, under the VSTS bug number 13741858.
I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan? - OR -  Is there a Trace Flag that can be used to turn  off the log of this error?


texts: Change only one cell value on select statement

texts:
i have these db tables:
 Table Combo:
id int identity(1,1)
name varchar
duration int
type int

table related
sourcecombo int
destinationcombo int
finalCombo int

sales
id int identity(1,1)
username varchar
comboid int
saledate datetime

Now, if a combo has a duration=0, it means that the client is upgrading or downgrading its product. In the interface, I need to display the destinactioncombo value, with the condition that the sales table has one record comboid, where duration=0.But I need to display the value only at the cell that has as a primary value sourceCombo. The query I am trying to use is the following:
    if exists
(select comboid
from sales
inner join combo on sales.comboid=combo.id
where combo.duration=0 and  username=@name)
select sales.username,saledate,
(select destinationcombo from related
where FinalCombo =
(select comboid
from sales where
saledate=(select max(saledate) from
Sales where username=@name)))
as comboid from Sales where username=@name
else select  '0'

where @name is a value taken by input from the user. The result i get until now, is the following:

The problem is, that it does capture the destinationCombo as it should, but it displays it at every single row when it has to display only at the last row.
UPDATE 1
So, the steps I should follow to achieve the result are like follows:

Check the sales table if it has any combo with duration=0 for
the selected user
If there is a result, then take the most recent combo (with
duration<>0 and with most recent saledate)
After finding that combo, than calculate from relation the
destinationcombo, where FinalCombo is the combo found on step 1) and
sourcecombo is the combo found on step 2).

Let's hope this clears the question a little more


texts: multiple insert queries after a set of with's

texts:
I am trying to have a reproducible way to populate a database with some test data.

Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-city, and name-job. I need to create an entry into each of three base tables and use the aforementioned entries to create entries in the TWO relationship tables.

What I have already is a way of using a series of with queries to create entries in the 3 base tables and insert values into ONE relationship table.

with x as
(INSERT INTO "public"."name" VALUES(DEFAULT) RETURNING "id"),
y as
(INSERT INTO "public"."job" VALUES(DEFAULT) RETURNING "id"),
z as
(INSERT INTO "public"."city" VALUES(DEFAULT) RETURNING "id")

INSERT INTO "public"."name-job"("name", "job")
select x.id, y.id from x,y;

I would really like to add a second insert statement

INSERT INTO "public"."name-city"("name", "city")
select x.id, z.id from x,z;

after the first insert but don't know how. I have tried separating the two insert statements with commas, and enclosing them in parentheses and then separating them with commas, and some other ways but nothing works.

It is not entirely an option to use a brand new statement with the second insert because I need the same values of x, y, z to be used. Given my lack of expertise/experience, it is entirely possible I am missing something obvious... so any ideas on how I can do this, ideally, without highly sophisticated tools, would be most welcome.

FWIW, I'm using Postgres (10.x)


texts: How to handle frequently outdated statistics dealing with an ORM?

texts:
We have a vendor application that uses Hibernate ORM (an object-relational mapping tool). It routinely generates queries returning hundreds of columns out of 10+ tables at a time. By logging the results of sp_WhoIsActive to a table every 5 minutes, I found that queries were often doing tens or hundreds of millions of reads. Updating the statistics DAILY using Ola Hallengren's "IndexOptimize" script seems to drop the reads down tremendously, but only for an hour or so before they get start to climb again.

Auto-create and auto-update statistics are both set to ON for the databases.

Is there anything I can do to help the situation other than updating statistics every hour or so during the business day? The job only takes 2 or 3 minutes. The queries are so complicated that the execution plans come back from sp_WhoIsActive as "Could not render showplan due to XML data type limitations".


texts:
The AD Group to which I belong has db_owner permissions on database LandingZone. The application I am debugging, that is running under my ID, is doing a SELECT NEXT VALUE FOR ThisSchema.ThatSequence. I am getting the following error:

Msg 229, Level 14, State 5, Line 6
The UPDATE permission was denied on the object 'ThatSequence', database 'LandingZone', schema 'ThisSchema'.

I think I'm running into a Least Privilege issue but the production DBAs disagree.

Any suggestions?


texts: Update stats takes significantly longer with a 50% sample rates versus a full scan

texts:
We have a home grown update stats job for large tables that essentially issues an UPDATE STATS command. Historically we had been defaulting to FULL SCAN, but recently we switched to SAMPLE 50 PERCENT. Oddly, the update stats command run times are drastically higher.

To give you an example, we have table 1 which has 6 statistics (3 index, 3 auto generated). Clustered index is 1.2 TB; NCI 1 is 2.7 GB; NCI 2 is 2.6 GB

The statistics were updated on the table 1 month ago with FULL SCAN and the command took 96 minutes. The statistics were updated last night with SAMPLE 50 PERCENT and the command took 593 minutes! Table row counts between the 2 runs were roughly the same.

I can see from sys.dm_db_stats_properties that the clustered index took all but 4 minutes of that time.  My question is, why would reducing the sample rate by 50% caused the command to run almost 5 times longer?

There wasn't any blocking occuring while the command ran (according to SQL Sentry) and there weren't any resource bottlenecks that jump at me (CPU < 40%, IO latency < 10 by-in-large).

One thing I'm wondering about is if parallelism is in play - with a full scan, can SQL use parallelism, but with a sample % it is single threaded?

We're running SQL 2012 SP2 CU7


texts: SQL Server Lock Escalation clarity required

texts:
I want to know whether Enabling the Trace Flag 1211 globally prevents the Lock Escalation even in the event of the Huge Memory Pressure.
I have 2 Questions regarding this:-

After Enabling Trace Flag do we still need to alter each and every Table in the User Database to Disable Page Locks.
Suppose if the answer is Yes then we would still need to disable Page Locks at each and every Non Clustered Indexes on the above Tables to prevent Page locks / Lock Escalation.


texts: Is SELECT FOR ... UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?

texts:
Consider the following when the transaction isolation level is Read Committed (the default):

UPDATE example
  SET foo = 'bar'
WHERE ...

and

DELETE FROM example
WHERE ...

Neither DELETE nor UPDATE supports ORDER BY, nor do they have a defined order. After reading the PostgreSQL docs extensively, it would seem the most simple and reliable solution to avoid deadlock in a concurrent environment is

UPDATE example
  SET foo = 'bar'
WHERE ctid IN (
  SELECT ctid
  FROM example
  WHERE ...
  ORDER BY ctid
  FOR UPDATE
)

I'm not thrilled about doing this to every UPDATE and DELETE that affects multiple rows.

Is my assessment and solution correct?
Is this the best solution?
Is there a reason PostgreSQL doesn't already do this automatically?


texts: What's a good way to model user authorization to hierarchical data?

texts:
I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012).  We're working with farm production information.  We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy.

Our data hierarchy looks like this:

System
- Farm
  - Group
    - Animal

The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System.  Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it.

Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires).

What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see.  Implemented here, it would look like this:

Table:  Authorizations          Table:  FullAuthorizations
Columns:    Id (PK)             Columns:    Id (PK)
            UserId                          UserId
            ObjectId                        SystemId
            ObjectType                      FarmId
                                            GroupId
                                            AnimalId

The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc).  The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data.  A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations.  I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records.

The queries would look something like:

SELECT *
FROM dbo.Animals a
WHERE EXISTS (
    SELECT 1
    FROM dbo.FullAuthorizations fa
    WHERE fa.UserId = 1 AND fa.AnimalId = a.Id
)

In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects.  I'd appreciate feedback on some other possible solutions.  I've been looking at things like Nested Sets, but not sure something like that fits this particular problem.


texts: How do I know which timescale db docker image was used?

texts: I see timescaledb.last_tuned_version  = '0.8.0' in postgresql.conf. How can I know which docker timescaledb image (exact version) was used?


texts: Unable to decompose this relation to BCNF

texts:
I have a relation

R = { A, B, C, D, E, F, G, H, I }

And functional dependencies

F ={
ABC -> DE
E -> C
AB -> F
C -> G
F -> H
H -> IJ
F -> B
}

I am able to do simple BCNF decomposition but I can't decompose this. I have ABC as the only candidate key. Then I split it into two relations to get rid of the first FD that breaks BCNF, E -> C, giving me the relations

{ A, B, D, E, F, G, H, I, J } and { E, C }

But now straight away I have lost the candidate key from the first relation. So does this mean I have now have to find a new candidate key for the first relation and then continue with the process of decomposing it until we are left with no relations that violate BCNF? Could someone show me how to work out this decomposition?


texts: How should I model a database structure to store word information on flashcards?

texts:
Words, base words, and flashcards

I recently posted this question, where I asked about modeling words and base_words in a database - now that I've landed upon having separate tables, a second issue has arisen: a user adds flashcards containing words, which link to a base word, and contains a full sentence from one of the texts in our database, along with other grammatical information from our database. I'd also like to keep track of how many words the user has learnt, as well as how many base words.

Considerations

Given that I'd like to keep a count (and keeping track) of the user's learnt words separately from learnt base_words, this involves having a separate table (I think). In this table, in terms of normalization/denormalization, is it ok to store all the user's learnt word_ids in one array, and then learnt base_word_ids in another array?
Given that the words, base words, and sentences will already be in the database, it seems pointless to duplicate the information onto their flashcards - however, given that the student needs to study the flashcards offline, does this require duplication, or am I misunderstanding database structures? My aim is that the student can study offline, and then when he comes back online, if there has been a change to the original DB data that he has on his flashcard, that it will be updated automatically. The only new information that will go on the flashcard is the user's personal notes.

Warm regards,
Michael


texts:
I'm struggling to find an optimal way of achieving this without lots of individual queries and PHP, I'm sure there must be an easier way of doing it.
To simplify we have the following table structure:
CREATE TABLE `log` (
  id INT UNSIGNED AUTO_INCREMENT,
  userid INT,
  itemid INT,
  eventname VARCHAR(30),
  timecreated INT
);

timecreated is a unix timestamp, eventname will be one of 2 statuses (assigned or unassigned).
Assuming the following data (I've put actual dates so it's easier to read):

id
userid
itemid
action
timecreated

1
1
1
assigned
2020-01-01

2
1
2
assigned
2020-01-01

3
1
1
unassigned
2020-03-01

4
1
1
assigned
2020-06-01

5
1
1
unassigned
2020-06-30

The output should be:

itemid
start
end

1
2020-01-01
2020-03-01

2
2020-01-01

1
2020-06-01
2020-06-30


texts: Record with invalid foreign key stored in PostgreSQL

texts:
In our product, there is, among others, a table Condition which contains a foreign key to table Rule.

We have an intermittent issue in our nightly test system where a record with an invalid RuleId somehow gets stored in the table Condition, i.e. there is no Rule record with such an ID.

I have checked the FK by inserting a new row into the table Condition with another invalid RuleID and the foreign key constraint is still being enforced.

The nightly test system is just a NUnit runner that runs an API test suite to test the app' API, nothing fancy. The database was created once without any DDL modification by the web app.

Is there any sequence of actions that can result in such a situation?