texts: SELECT in trigger function in two tables

texts:
My table and trigger in  PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
    );

CREATE TABLE file_headers (
        header_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        file_header_index_start INTEGER,
        file_header_index_end INTEGER
    );

CREATE TRIGGER measurement_ids AFTER INSERT
        ON measurements FOR EACH ROW
        EXECUTE PROCEDURE ins_function('SELECT measurement_id FROM measurements
        ORDER BY measurement_id desc limit 1;', 1, 666 );

where I assumed that the datatype of the SELECT is INTEGER since SERIAL but it is apparently false because I get the error message from this command which starts the trigger:
INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);`

ERROR:  invalid input syntax for integer: "SELECT measurement_id FROM measurements ORDER BY measurement_id desc limit 1;"
CONTEXT:  PL/pgSQL function ins_function() line 10 at assignment

Edit
ins_function() and edits based on @a_horse_with_no_name and @Joishi's comments:
CREATE OR REPLACE FUNCTION ins_function() RETURNS TRIGGER AS $$
    --
    -- Perform AFTER INSERT operation on file_header by creating rows with new.measurement_id, new.file_header_index_start and new.file_header_index_end.
    --
DECLARE
    measurement_id              INTEGER;
    file_header_index_start     INTEGER;
    file_header_index_end       INTEGER;
BEGIN

    SELECT a.measurement_id INTO measurement_id from measurements a ORDER BY measurement_id desc limit 1;
    file_header_index_start := TG_ARGV[0];
    file_header_index_end := TG_ARGV[1];

    IF TG_OP = 'INSERT' THEN
        INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end)
        VALUES (measurement_id, file_header_index_start, file_header_index_end);
        RETURN NEW;
    END IF;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

--
-- Function and trigger on INSERT.
--
CREATE TRIGGER measurement_ids AFTER INSERT
    ON measurements FOR EACH ROW EXECUTE PROCEDURE ins_function(1, 666);

I get now no error but the output is wrong: no INSERT seen in the table file_headers while successfully in the table measurements.
Output of @ErwinBrandstetter's answer
So I started to think about casting from TEXT to INT but this should be so basic operation, since TG_ARGV[] is a datatype of text. One unsuccessful try is format('SELECT $1.%I', TG_ARGV[0]).
The regclass could work as you describe here in insaft_function()
SELECT NEW.measurement_id, TG_ARGV[0]::regclass, TG_ARGV[1]::regclass;

Why are there no successful INSERTs into the table file_headers?


texts: How to enable CLR on a remote SQL Server?

texts:
I have been created a dll for SQL Server 2014 to add some custom functions for my stored procedures for my asp.net websites.

I don't have any problem in the local server but can't enable the CLR features on a remote host database; trying to do so throws an error that User does not have permission to perform this action.

So I call host administrative and they told me I can use only virtual servers or personal server to enable this feature. But virtual servers monthly charges and personal servers are very expensive.

So I want to know to are there anyways exist to add dll to the remote host and using them somehow?


texts: Is there a way to restrict automatic statistics updates to a certain timeframe?

texts:
I have an Azure SQL Database that runs on average at < 10% DTU capacity. It seems that at random times during the day large statistics update queries will execute on the database, causing DTU (specifically data IO) resources to spike.

I have confirmed that these are statistics updates coming from the system by looking at the "Query performance insights" graphs and reports.

For example, one of these queries is:

SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.064647e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Sometimes these statistics updates can have a negative effect on the production application running on top of this database. Execution times against the database and application can increase.

Since I cannot predict when these daily statistics updates will happen, it is not possible for me to schedule a maintenance window where I can increase system resources to help them complete faster. Obviously at < 10% average resource usage I am not at a point where it makes sense to increase resources and pay for a costlier tier 24/7.

I already run a scheduled job weekly during off hours (on the weekend) to rebuild indexes and update statistics manually, yet these automatic updates still seem to happen on a daily basis, often during peak times.

Is there a way I can reduce the impact of these automatic statistics updates during peak application times? Or is there a way I can better predict when they will happen? Ideally I would like to handle these types of impactful maintenance tasks during a planned window.


texts: Need help on table design how to consider when an item from table_a is referenced on table_b

texts:
i'm out of idea. i first want to inform everyone that i am in no way a DBA. assuming i have table design as below:

table_a:
--------------------------------------------------------
- id - ref_no - date_added - added_by - void_record_id -
--------------------------------------------------------
-  1 - dt_001 - 2019-01-01 -        1 -              0 -
-  2 - dt_002 - 2019-01-01 -        1 -              0 -
-  3 - dt_003 - 2019-01-02 -        1 -              0 -
-  4 - dt_004 - 2019-01-02 -        1 -              0 -
-  5 - dt_005 - 2019-01-03 -        2 -              1 -
--------------------------------------------------------

table_b:
------------------------------------------------------------
- id - table_a_id - date_added - added_by - void_record_id -
------------------------------------------------------------
-  1 -          1 - 2019-01-04 -        1 -              0 -
-  2 -          3 - 2019-01-04 -        1 -              0 -
------------------------------------------------------------

void_record:
----------------------------------------------------
- id - void_by - date_added - remarks              -
----------------------------------------------------
-  1 -       3 - 2019-01-05 - removed from records -
----------------------------------------------------

think of table_a as some sort of a repository for all items added to the database. table_b then acts as a log for what items a user took from table_a. void_record then contains all voiding logs that happened. this is done instead of deleting the row, so i still have a history of what happened to the specific item (was it voided? how many times? by who? and when? etc). if void_record_id is set to 0, then it means the data on table_1 is valid. otherwise, it is invalid or is a "voided record".

now the thing is, a module i'm working on displays all records from table_a WHERE void_record_id = 0;. with records 1 and 3 of table_a now referenced in table_b, how can i cancel them out of table_a without voiding them out since they were "took" not "voided". These referenced items should no longer appear on the application since they are already in use.

i am open to the idea of adding columns, or tables. but the former would be better.

sorry about that.

cheers.


texts:
I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the current one since MySQL does not support materialized views), this is done with a stored procedure which runs after every insert.

Would it be possible to run the procedure automatically after committing the transaction?


texts:
I have the following table:

CREATE TABLE `twitter_relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `source_and_target` (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index` (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

This table is pretty big, ~150M records.

And I have the following query:

SELECT target_twitter_id
 FROM `twitter_relationships`
WHERE (`twitter_relationships`.`relationship_status` = ?
   AND `twitter_relationships`.`user_id` = ?
   AND `twitter_relationships`.`source_twitter_id` = ?)
LIMIT ?, ?

Here is the explain for this query:

           id: 1
  select_type: SIMPLE
        table: twitter_relationships
         type: ref
possible_keys: source_and_target,target_status_and_change_date_index,user_id_index
          key: source_and_target
      key_len: 12
          ref: const,const
         rows: 8560582
        Extra: Using where

Any ideas what I can do in the query or even in the table structure to speed this query up?


texts: How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

texts:
I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format?

I found this question, but it shows only how to convert timestamp without time zone to unix timestamp.


texts: I want to know if SQL Server 2012 protect msdb.dbo.sp_send_dbmail parameters?

texts:
I use this system stored procedure dbo.sp_send_dbmail to send email from my database, I want to know if this procedure is protect against SQL Injection?

And if you have some reference this will be util for me.

Thanks in advance.


texts: Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

texts:
I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.

A table could look like this:

CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
    [id] [int] NOT NULL,
    [description] [varchar](20) NOT NULL,
    [datetime] [datetime] NULL,
CONSTRAINT [pk_bulk_insert_datetime_issue] PRIMARY KEY CLUSTERED (
    [id] ASC
))

The insert batch:

BULK
INSERT [dbo].[tbl_bulk_insert_datetime_issue]
FROM 'C:\temp\bulkinsertsample.csv'
WITH
(
    FIELDTERMINATOR=';'
)

If I am to insert the following CSV content:

1;row01;
2;row02;20130401
3;row03;
4;row04;20130515

The table' content was parsed and inserted as expected:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                NULL
4           row04                2013-05-15 00:00:00.000

But if the CSV file contains invalid data for the optional datetime

1;row01;
2;row02;20130401
3;row03;not_a_datetime
4;row04;20130515

SQL Server inserts the last valid datetime value instead of discarding the row with an error message:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                2013-04-01 00:00:00.000
4           row04                2013-05-15 00:00:00.000

If I am to insert a row with an invalid datetime and no valid parsable datetime at all

1;row01;not_a_datetime

SQL Server inserts the default value of DATETIME:

id          description          datetime
----------- -------------------- -----------------------
1           row01                1900-01-01 00:00:00.000

Why does Sql Server insert the (for that specific row) invalid datetime when using bulk insert?
If I try to insert rubbish to an INT column the row will not be inserted by the bulk batch and an error will be thrown...

UPDATE:
I will share some information about the environment I used to reproduce the behaviour.

OS: Windows 7 Enterprise SP1 x64
regional settings: de-DE
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Enterprise Edition
database collation: Latin1_General_100_CS_AS
file encoding: UCS-2 Little Endian (with Windows CRLF)

As hinted by Max Vernon the file encoding seems to be at fault. Setting the encoding to UTF-8 without BOM the bulk insert will throw the error I expected and discards the invalid row.


texts:
From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date)

How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, page_id) and also the value of created_at when min(lines) and the same for max value.

Dummy data:

type_id, book_id, title_id, page_id, lines, created_at
1, 1, 1, 1, 12, 2015-04-01
1, 1, 1, 1, 10, 2015-03-01
1, 1, 1, 1, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01
1, 1, 1, 2, 5, 2015-03-01
1, 1, 1, 2, 6, 2015-03-04

Expected output:

type_id, book_id, title_id, page_id, min, min_created_at, max, max_created_at
1, 1, 1, 1, 10, 2015-03-01, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01, 6, 2015-03-04


texts: Is there some tricks to better index a URL column in postgres?

texts:
I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.:

http://www.google.com
http://www.amazon.com
http://www.stackoverflow.com

I was thinking if indexing urls this way would not degree performance for b-tree index. Then I raised some options but not sure if will make some improvement:

Add another column removing http://www. to apply index
Add another column with some hash function like md5(url) to apply index

Does this will have some gain on performance?
Thanks in advance!


texts:
Are there any options for running DDL triggers in a MySQL database. I would like to create a view every time someone create a table.

If the "Users" table is created, I want the DDL trigger to create a view called "v_Users".


texts: Issue with MDS data when viewed in Excel addin after connecting to MDS Model

texts:
In MDS 2012, while exploring for a entity, I can see the data in Client and Product column are properly assigned as Code and Code {Name} as seen below:

but while connecting to the MDS model via Excel addin I am not able to see the Product column with Code and Code {Name} data which I can see for Client column in Excel sheet as Code and Code {Name} data. Please see below:

I have verified the settings of Product with the Client attributes in the MDS. but couldn't see any issue with it.

Can anyone help me with this or anyone has come across this issue before?


texts: Selecting unique rows based on the maximum date

texts:
I am using PostgreSQL and I am trying to create a view of data:

forecast_datetime, file_date, thing_id, thing_name, facts...

The forecast_datetime column is a datetime for each 30 minute interval. The issue is that the forecast is for 1 month into the future which causes a lot of duplicate data. (so today, there will be a forecast for 2016-12-25 01:30:00 and then tomorrow there will be a forecast for the same interval, over and over again potentially until the period actually happens.).

The view I want to create is to just take a look at the last forecast which was appended to the table. This is indicated by the file_date column (as the name suggests, I receive a daily forecast file which is appended to the database. This column indicates the date from the filename).

I basically want to filter all rows for each thing_id and select the forecast_datetime columns where there file_date was the maximum date for that forecast. So each thing_id should only have one row per unique forecast_datetime interval which is based on the latest file_date.

CREATE OR REPLACE VIEW schema.view AS
SELECT DISTINCT ON (thing_id, forecast_datetime, file_date)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id,
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  file_date DESC

Would that query do the trick? I can do this in pandas/python easily, but I am newer to SQL and wanted to try a view instead of writing a new physical table.

Here is some sample data after applying the query I wrote above this. Because I included the the file_date in the SELECT DISTINCT query, there is still a repeat row which duplicates each forecast_datetime. This causes issues when I join this data based on the thing_id and forecast_datetime back to the actual results table.

forecast_datetime   file_date   thing_id
2016-12-12 20:30    2016-12-11  99
2016-12-12 20:30    2016-12-10  99
2016-12-12 20:30    2016-12-09  99
2016-12-12 20:30    2016-12-08  99

I would like to only have the row with the max file_date. In the example above, that would be the first row (2016-12-11). The other rows would be dropped from the view.


texts: How to export an Access database and import to MySQL?

texts: I am working on a Java project that sources data from an MS Access database, but I want to use MySQL because I have found it to be very easy to manage. How can I export an Access database that has tables of 8 MB and more, and move the data to MySQL?


texts: Is there a combination of columns in sys.dm_exec_sessions that is unique per the server?

texts:
In SQL Server, each session has its own spid. Spids are unique at any given notice, but spids, like process and thread identifiers in the OS are recycled.

However sys.dm_exec_sessions has other columns with session metadata. Is there a combination of columns that is guaranteed to be unique for a server instance?


texts:
After installing Oracle and opening Command Prompt, I typed in
sqlplus "/as sysdba"
and got an error message
ERROR:
ORA-01031: insufficient privileges
and then it asks me to enter the user-name.
I'm using my own personal computer and although the name given after C:\Users\ is not called "Administrator", it's my username when I used to log in to this computer and since I'm the only one using the computer I'm assuming it's the same as "Administrator" and when I opened account settings it says administrator under my account name.

Microsoft Windows [Version 10.0.17134.228]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\linus>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 20 10:56:53 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

What might be the cause of this and what can I do to solve it? Thanks!


texts:
I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.

We have a single table that holds relationships between different parties.  The table SHOULD contain a row for each relationship.  However, we've noticed that some relationships are missing and we want to rebuild those.  Essentially there should be two rows per relationship, the relationship and the inverse.

So, a healthy table should look like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   4444      3344            1         2

But, we have a situation like this...

Id | PartyId | OtherPartyId | RelType | RelStatus
1   1111      2211            1         1
2   2211      1111            1         1
3   3344      4444            1         2
4   5555      2224            1         2
5   2224      5555            1         2

The inverse of the relationship between party 3344 and 4444 is missing, so I want to return that row only so that I can insert the inverse relationship.

The 'id' column is immaterial as it is only an identity column, but RelType and RelStatus are important as these need to be the same for the inverse relationship - example being that there could be a relationship that DOES exist between 4444 and 3344 but it is of a different type and status so we should ignore that (unless of course it has a missing inverse too!).

I tried to create a composite key system to try and identify the missing rels but I found it brought back duplicates in some instances:

            SELECT
        CASE WHEN [PartyId] < [OtherPartyId]
            THEN CAST([OtherPartyId] AS NVARCHAR(50)) + '.' + CAST([PartyId] AS NVARCHAR(50)) + '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50))
            ELSE CAST([PartyId] AS NVARCHAR(50)) +  '.'  + CAST([OtherPartyId] AS NVARCHAR(50)) +  '.' + CAST([RelType] AS NVARCHAR(50)) +  '.' + CAST([RelStatus] AS NVARCHAR(50))
        END AS CompKey
        INTO #partyinvs
        FROM PartyRelationships;

        SELECT  CompKey
        FROM    #partyinvs
        GROUP BY CompKey
        HAVING  COUNT(*) < 2

I hope this makes sense?

Thanks,
David


texts: Adding a disabled foreign key to InnoDB table

texts:
The concrete problem is having a vast database which is lacking foreign keys, which makes it difficult for developers to work on.
The plan was to add disabled foreign keys to it initially, which would allow generating EER diagrams from the DB and would increase developer productivity, but from what I could find, it is not possible to have disabled foreign keys when using the InnoDB storage engine.
A solution to the DB understanding problem could be creating an EER diagram using MySQL Workbench and sharing it, but that would cause a lot of issues regarding synchronization, awareness of it, etc.
Long term plan would be to enable the FKs over time with all the necessary precautions.
Is there any way I can create disabled FKs when using InnoDB?
What other suggestion would you have for the concrete problem of generating DB diagrams with FKs connecting the tables?


texts: MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different?

texts:
I have a database with two DATETIME values, and then I generate some new values from them:

tdiff = timediff(d1,d2)+0
day1 = truncate(tdiff/60.0/60.0/24.0,0)
day2 = datediff(d1,d2)+0

All three of those "variables" are FLOAT fields in the (temporary) database. I would expect that day1 and day2 would never differ by more than 1 or 2. However, there are certain values that seem to generate a wild answer - and they tend to be for certain values of tdiff. Calculating the value from tdiff shows that the calculations are right.

Conceptually, day2 should be difference between the dates from the DATETIME values, and day1 should be the number of 24-hour periods in the same time frame.

The biggest differences are at the maximum value for timediff: using calculations, the number of 24-hour periods is just over 97. However, using the DATEDIFF function shows a time span of as low as 60 days. This makes no sense.

There are also big differences when TIMEDIFF results in 26 24-hour periods: DATEDIFF reports 10 days instead - or when TIMEDIFF results in 30 24-hour periods, and DATEDIFF reports 11 days. There are also differences when TIMEDIFF reports 79 days, but DATEDIFF gives 29 days.

Here is example output - from a Ruby script (and in Ruby array format) - with columns d1,d2,TIMEDIFF,24-hr periods,DATEDIFF:

["2012-04-11 06:15:21", "2012-02-09 22:11:21", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:22", "2012-02-09 22:13:24", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:25", "2012-02-09 22:15:29", "8.38596e+06", "97", "62"]
["2012-04-11 05:47:25", "2012-03-31 05:07:01", "2.64402e+06", "30", "11"]
["2012-04-11 05:47:26", "2012-03-31 05:09:03", "2.64382e+06", "30", "11"]
["2012-04-11 05:47:27", "2012-03-31 05:27:21", "2.64201e+06", "30", "11"]
["2012-04-11 05:48:43", "2012-01-27 16:39:32", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:00", "2012-04-01 19:58:00", "2.2551e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:00:02", "2.25486e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:02:04", "2.25466e+06", "26", "10"]
["2012-04-11 05:49:05", "2012-04-01 20:04:04", "2.2545e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-04-01 20:06:06", "2.2543e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-01-27 17:36:07", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:07", "2012-04-01 20:08:10", "2.25406e+06", "26", "10"]
["2012-04-11 06:15:06", "2012-03-13 12:23:24", "6.89514e+06", "79", "29"]
["2012-04-11 06:15:07", "2012-03-13 12:25:25", "6.89494e+06", "79", "29"]

Why are these calculations so different? I am assuming that it is a lack of understanding of TIMEDIFF, but what am I missing?


texts:
I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously).

For non-technical reasons, the server needed to be immediately brought back up, with literally minutes to work out a solution, I found and used pg_resetxlog - just restart the WAL archives. My reasoning was that the server was not in active use - it being a weekend, so there was nothing much that could be lost.

According to the documentation (http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html ) pg_resetxlog should really not be used, and when it is used, a DB dump should be taken and restored immediately after.

My question is, is this a concern for any transactions that occurred in the last seconds - or is it a concern that could ultimately hurt the server as a whole? Is it possible that on an inactive server (with a few routine logs written via cron the most that could have possibly been lost), there was no harm done?

The database will be in heavy use for the next day or so, can I do the dump and restore after that if it is necessary?


texts: What happens when InnoDB hits its tablespace autoextend max?

texts:
The latest version of High Performance MySQL (3rd ed.) recommends that one:

  ...place an upper limit on the tablespace's size to keep it from growing
  very large, because once it grows, it doesn't shrink.

They give the example of:

...ibdata3:1G:autoextend:max:2G

But it isn't clear to me what happens if your dataset keeps growing and hits this maximum. Unfortunately the MySQL docs aren't much help either.

Can someone shed some light on this? It seems like this advice is kind of dangerous on the face of it.

Thanks!


texts:
What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?
There are plenty of sites discussing how to view the SYSAUX occupants, to reduce the size of SYSAUX via dbms_stat.purge_stats(), to add datafiles to SYSAUX tablespace, and to alter_stats_history_retention to avoid the problem in the future.  This is not what is being asked here.
What are the consequences of not doing any of the previously mentioned tasks, but still utilizing the database?  I've read that SYSAUX is important, but not critical (critical stuff is in SYSTEM).  However, I have not seen any further discussion about what are the consequences of continuing to operate with a full SYSAUX.
For example, will performance be an issue?  If so, in what ways?
Does Oracle stop writing to SYSAUX or does Oracle try to delete just enough "old" items to make room for "new" items? (most likely it depends on what it is, but don't see any documentation or article that describes the resulting behavior of a full SYSAUX tablespace and what the resulting consequences may be and what to watch out for).
If one gathers stats will Oracle purge the oldest historical stats to make room for the new ones?  If so, is it true MMON gets called to do this "on-the-fly" rather than during the nightly process to maintain the retention setting.  If so, is this costly if an application is doing a lot of stat gathering and to what extent is it costly?
Thanks in advance.
EDIT: Please assume that AUDSYS schema is not in SYSAUX tablespace.


texts: Design for CE Events / Training Database

texts:
I'm working on a design for a continuing education training database.  Users will register for a CE course/event that could be one day or multiple days.  Courses are taught multiple times per year.
The part I wanted some review on is the calendar part of the DB.
Here is the design criteria:

Multiple events taught on varying schedules throughout the year.
Classes/Events can be part of a day, one day or multiple days.
Times can be different across days for the same Instance.  Ex. "Summer xyz Training" held 9am-5pm Monday, 9-noon Tuesday, etc.

Here is my design for this part of the DB so far:
Event
Class, event or course... can be scheduled multiple times... taught at various times, dates, and locations repeating throughout the year.

Event_ID - Could be called Class_ID. Could be class or event.
I went with Event_ID to handle either.
Name - Ex. "xyz Training"
Description -
Objectives - yes - hidden
Outline - yes - hidden
Duration_hours  ( Break up by Class Schedule )
Notes - Hidden from user

Event_Instance
Instance of an event.  Can be across multiple days.  Event_Instance_Schedule for the Instance must add up to Event.Duration_Hours

Event_Instance_ID
Location_ID - Tie to Location table for where (could be physical or on line)
Description - Ex.  "Summer xyz training"

Event_Instance_Schedule
Block of time as part of Event_Instance that adds to total Duration_Hours.

Event_ID
Event_Instance_ID -
Event_Instance_Schedule_ID
Instructor_ID?
Start_Date_Time - Ex. "1/1/2017 8am"
End_Date_Time -  Ex.  "1/1/2017 5pm"
Notes

I don't really need to setup Google Calendar type re-occurrence, thankfully.  My thinking was that the user chooses a class, gives that instance a title like "Summer xyz training", and then schedules times until they reach the Duration_hours number.
I think it's fairly simple. Just looking for feedback or "have you thought about this... " feedback, or "gotchas" I'm not considering.
I have reviewed a number of event / class schedule DB designs, but none quite fit what I'm doing...too much or too little.  The question is meant to be at a high level regardless of the DB technology (MySQL, SQL Server, etc.)
The DDL is the easy part in my mind, but I should probably do that based on a good design.  I just wondered if anyone had done an Event DB kind of thing before and if they had feedback based on my initial concept.


texts:
What permissions are minimum needed to allow developers to create and view  Xevents sessions on an SSAS instance.
Do they need Full control on the instance or can this be done with less permissions?
SSAS Version:  Microsoft Analyses server 2016.
When it can be done with less than full control, do you have the XMLA code please?


texts: How to create relationship metadata between tables without a foreign key check?

texts:
I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with following (simplified) use case:

Tables

table A (with ID)
table B (with ID, ColumnA, ColumnC)
table C (with ID)

All ID Columns are unique, ColumnA and ColumnC have duplicate values

Situation

ColumnC has a subset of values from C, in other words, B will never have a value that C doesn't.

ColumnA has a non-perfect superset of values from A, in other words, B has
 exclusive values and almost all values of A, but A also rarely has exclusive values.

Problem

There is a Foreign Key between B.ColumnC referencing C.ID, which works as intended.

Now, because of the nature of Foreign Keys, there is no Foreign Key on B.ColumnA referencing A.ID, because B has values that A don't.

Question

Basically, I am having problem with some "many to many"-like relationships.

I thought about disabling Foreign Key checks and then creating a Foreign Key on B.ColumnA, but I haven't found a way to disable checks on only one specific contraint, because I still want to maintain data integrity on B.ColumnC Foreign Key.

Is there a way to create a relationship between B and A without losing data integrity and preferably without creating any additional tables or columns?

Edit: I am working with Postgres, but I will tag this with "database agnostic" to see if solutions from other vendors are portable to my case.


texts:
I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error.

The Merge Agent failed because the schema of the article
at the Publisher does not match the schema of the article at the Subscriber.
This can occur when there are pending DDL changes waiting
to be applied at the   Subscriber.
Restart the Merge Agent to apply the DDL changes and
synchronize the subscription.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)


texts:
I have a SQL Server 2016 instance and I've just noticed that some user logins aren't listed for some users. For example, if I execute this query:

select count(*) FROM sys.database_principals a where type = 'U'

I receive a count of 1348. But if I execute this query:

execute as user = 'some_user';
select count(*) FROM sys.database_principals a where type = 'U';
revert;

I receive a count of 92, meaning that there are 1256 users that can't be seen by the above user. If I grant the user db_accessadmin database role membership, then they're able to see all 1348 users returned from that above query. However I do not want to grant db_accessadmin to all users as that gives much more access than I want to grant.

I've compared the properties of one of the 92 users that does show up to the properties of the users that don't show up however I haven't noticed any property that looks to allow listing the user.

How may I go about allowing all users to be listed in the query to database_principals?


texts: Fastest way to count how many dates' ranges cover each date from series

texts:
I have a table (in PostgreSQL 9.4) that looks like this:

CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
    (1, '2018-01-01', '2018-01-31'),
    (1, '2018-01-01', '2018-01-05'),
    (1, '2018-01-03', '2018-01-06'),
    (2, '2018-01-01', '2018-01-01'),
    (2, '2018-01-01', '2018-01-02'),
    (3, '2018-01-02', '2018-01-08'),
    (3, '2018-01-05', '2018-01-10');

Now I want to calculate for the given dates and for every kind, into how many rows from dates_ranges each date falls. Zeros could be possibly omitted.

Desired result:

+-------+------------+----+
|  kind | as_of_date |  n |
+-------+------------+----+
|     1 | 2018-01-01 |  2 |
|     1 | 2018-01-02 |  2 |
|     1 | 2018-01-03 |  3 |
|     2 | 2018-01-01 |  2 |
|     2 | 2018-01-02 |  1 |
|     3 | 2018-01-02 |  1 |
|     3 | 2018-01-03 |  1 |
+-------+------------+----+

I've come up with two solutions, one with LEFT JOIN and GROUP BY

SELECT
kind, as_of_date, COUNT(*) n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates
LEFT JOIN
    dates_ranges ON dates.as_of_date BETWEEN start_date AND end_date
GROUP BY 1,2 ORDER BY 1,2

and one with LATERAL, which is slightly faster:

SELECT
    kind, as_of_date, n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates,
LATERAL
    (SELECT kind, COUNT(*) AS n FROM dates_ranges WHERE dates.as_of_date BETWEEN start_date AND end_date GROUP BY kind) ss
ORDER BY kind, as_of_date

I'm wondering is it any better way to write this query? And how to include pairs date-kind with 0 count?

In reality there is a few distinct kinds, period of up to five years (1800 dates), and ~30k rows in dates_ranges table (but it could grow significantly).

There are no indexes. To be precise in my case it's a result of subquery, but I've wanted to limit question to one issue, so it's more general.


texts: How to use a different vlan for mirroring in an availability group

texts:
I want to setup the following with SQL Server 2014 Enterprise and Windows Server 2012 R2.
I have two nodes and a fileshare for witness. Both are virtual machines in the same vsphere cluster. To minize the network traffic in our network, the network administrators told me to use a seperate, unrouted vlan for the mirroring of the databases in the AG with a seperate NIC.

Host1 (primary):

NIC1 = 10.0.111.21 (routet, clientaccess)
NIC2 = 10.0.211.21 (nonroutet, mirroring)

Host2 (secondary):

NIC1 = 10.0.111.22 (routet, clientaccess)
NIC2 = 10.0.211.22 (nonroutet, mirroring)

I have tried to use the second nic as the endpoints and wrote a custom create script:

:CONNECT Host1

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.211.21))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

GO

:CONNECT Host2

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.211.22))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

GO

After that I have created the AG:

:CONNECT Host1
CREATE AVAILABILITY GROUP [AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [TestDB1]
REPLICA ON N'Host1' WITH (ENDPOINT_URL = N'TCP://10.0.211.21:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
       N'Host2' WITH (ENDPOINT_URL = N'TCP://10.0.211.22:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

The script runs and runs and after 5 minutes I got the error, that the second host can't connect to the first to synchronizise.

Can anyone please help me to setup the ag in a different vlan?


texts: Mysql self Join query with Limit never terminates

texts:
I'm running an experiment with the query
Select  Distinct table_1.id1, table_2.id1
    FROM  image as table_1, image as table_2
    WHERE  table_1.id2 = table_2.id2
    LIMIT  K;

When I run the query without limit, it terminates after 8 hours. However, the query does not terminate for Limit 80000.
I'm running this on cloudLab with 100GB Ram and Below is the image of my.cnf and query plan. I'm not sure what is the bottle neck for my query. How should I solve this problem
Query Plan text:
| -> Limit: 80000 row(s)
  -> Table scan on <temporary>
        -> Temporary table with deduplication
            -> Limit table size: 80000 unique row(s)
                -> Inner hash join (table_2.id2 = table_1.id2)  (cost=48913990425076.15 rows=48913988916980)
                    -> Table scan on table_2  (cost=0.01 rows=22116507)
                    -> Hash
                       -> Table scan on table_1  (cost=2224194.70 rows=22116507)


texts: CPU Spike 150% on postgresql Google Cloud SQL

texts:
I get random spikes running to 150% and it settles down in 2 mins
We have a postgres 9.6 database with 2TB data. Running on 48 cores and 180GB Ram

I have checked database logs,
I have set up cron job to run every min on pg_stats to capture any query running more than 2 sec ..
Checked google query insights

During these spikes I don't see any locks, just frequent queries getting affected (normal execution = 25mili, 45mils during spike.. )
I run this query every min via cron
SELECT (now() - pg_stat_activity.query_start)::text AS duration
       , concat('state : ', state)::text as state, usename, 'S' as action_step, 'U' as action
      , query
FROM pg_stat_activity
WHERE  query NOT ILIKE '%pg_stat_activity%'
and usename NOT ILIKE '%cloudsqladmin%'
AND (now() - pg_stat_activity.query_start) > interval '2 seconds'
and state ='active' ;

Question: is there anything else to check or see for what could be causing these spikes?


texts:
I have following different tables
LeafChilds
ChildId, ChildName
1, Child1
2, Child2
Group
GroupId, GroupName
1, Group1
2, Group2
3, All
GroupChildMapping
GroupId, ChildId
1, 1
2, 1
2, 2
GroupGroupMapping
GroupId, ParentGroupId
1, 3
1, 3
I am looking for query to find indirect parents for LeafChilds.
In above example - child1 is in Group1 and Group1 has parent group as ALL. So indirectly child1 is in ALL group as well.
OUTPUT
Child1, Group1
Child1, ALL


texts:
I'm making a simple airline reservation system. But I'm not sure about the design decisions I made. Concretely, I'm trying to implement following constraints:

each Aircraft can have one or more TravelClasses
each Aircraft has many Seats.
each Flight has only one AircraftInstance (of Aircraft)
prices for Flight differ for TravelClasses that are known through AircraftInstance assigned to Flight
Client must know which seats are available for Flight booking and a price of the seat

What I have for now:

Please, leave suggestions. Thank you!


texts: Connection problems with ManagePartition executable

texts:
I'm currently running SQL Server 2014 and we have some tables that have been partitioned and I'm trying to use the ManagePartition application here (http://sqlpartitionmgmt.codeplex.com/)

I've typed into the command line:

C:\>ManagePartition.exe /C:CreateStagingFull /d:<Databasename> /s:<schema>
                        /t:<TableName> /p:5 /A:<NewTablename> /f:C:/scripts.sql
                        /O:i /U:<My Login> /P:/<My Password>

And I'm getting the following error:

  ConnectionFailureException: Failed to connect to server (local). ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I've doubled checked all the spellings (dbname, schema, table, etc...) and the switches.

What are some things I can do to trouble shoot this (I'm kind of new to table partitioning)?

Or are there other strategies to easily manage/move data from partitioned table to a staging table?

Edit(I missed to the /S: switch)

I'm now getting this error:

  System.NullReferenceException: Object reference not set to an instance of an object.
  at PartitionManagement.PartitionManager.createStgIndex(Index i, TableViewBase parent)
  at PartitionManagement.PartitionManager.createStgNonclusteredIndexes()
  at PartitionManagement.PartitionManager.CreateStgIndexes(Boolean createNonClusteredIndexes)
  at PartitionManagement.App.Run(AppArgs& parsedArgs)
  Object reference not set to an instance of an object.

It did create the new table, with the indexes, constraints(including the partition constraint), but no data.


texts: Help with set up replication distribution database in Always On availability group

texts:
After following official microsoft guide and sample walkthrough:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017
https://repltalk.com/2019/03/09/walkthrough-publisher-distributor-subscriber-in-alwayson-availability-groups/

I run into a problem where my 'distrubution' DB on remote server (secondary replica) is not is sync with primary replica. I see below error:

And my AG dashboard:

And my actual script I use for this set up:

:setvar Dist_Pass "'Sa12345678'"
:setvar Primary_Server "D-MSSQL01-DE\SQL2019"
:setvar Secondary_Server "D-MSSQL02-DE\SQL2019"
:setvar Primary_Replica "'D-MSSQL01-DE\SQL2019'"
:setvar Secondary_Replica "'D-MSSQL02-DE\SQL2019'"
:setvar AG_Group "ag2019"
:setvar Dist_Listener "'D-AG04-PP,1436'"
:setvar DistributionDB_SqlData "'D:\SQLData_2019\'"
:setvar DistributionDB_SqlLog "'D:\SQLLogs_2019\'"
:setvar DistServerWorkFolder "'D:\SqlServerReplicationWorkDir'"
:Connect $(Primary_Server)
sp_adddistributor @distributor = @@ServerName, @password = $(Dist_Pass)
Go
:Connect $(Secondary_Server)
sp_adddistributor @distributor = @@ServerName, @password = $(Dist_Pass)
Go
:Connect $(Primary_Server)
EXEC sp_adddistributiondb @database = N'distribution', @data_folder =
$(DistributionDB_SqlData), @log_folder = $(DistributionDB_SqlLog),
@log_file_size = 2, @min_distretention = 0, @max_distretention = 72,
@history_retention = 48, @security_mode = 1
GO
ALTER DATABASE [distribution] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE [distribution]
TO
DISK = N'D:\Backup\distribution.bak'
WITH NOFORMAT, NOINIT,NAME = N'distribution-Full Database Backup', SKIP,
NOREWIND, NOUNLOAD,STATS = 10
GO
BACKUP LOG [distribution] TO
DISK = N'D:\Backup\distribution.trn'
WITH NOFORMAT, NOINIT,NAME = N'distribution-LOG Database Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
:Connect $(Primary_Server)
USE [master]
GO
ALTER AVAILABILITY GROUP $(AG_Group)
MODIFY REPLICA ON $(Secondary_Replica) WITH (SEEDING_MODE = AUTOMATIC)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP $(AG_Group)
ADD DATABASE [distribution];
GO
:Connect $(Secondary_Server)
--ALTER AVAILABILITY GROUP $(AG_Group) JOIN
--GO
ALTER AVAILABILITY GROUP $(AG_Group) GRANT CREATE ANY DATABASE;
GO
EXEC sp_adddistributiondb @database = 'distribution', @security_mode = 1;
GO
:Connect $(Primary_Server)
EXEC sp_adddistpublisher @publisher = $(Primary_Replica), @distribution_db =
N'distribution', @security_mode = 1, @working_directory =
$(DistServerWorkFolder), @trusted = N'false', @thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
EXEC sp_adddistpublisher @publisher = $(Secondary_Replica), @distribution_db
= N'distribution', @security_mode = 1, @working_directory =
$(DistServerWorkFolder), @trusted = N'false', @thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
:Connect $(Secondary_Server)
EXEC sp_adddistpublisher @publisher = $(Primary_Replica), @distribution_db =
N'distribution', @security_mode = 1, @working_directory =
$(DistServerWorkFolder), @trusted = N'false', @thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
EXEC sp_adddistpublisher @publisher = $(Secondary_Replica), @distribution_db
= N'distribution', @security_mode = 1, @working_directory =
$(DistServerWorkFolder), @trusted = N'false', @thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO

Points to note:

I run the script using F5 on primary node (which is my distributor)


texts:
We have a 5 node cluster in Cassandra in which we have created a keyspace with RF=3.

After creating a column-family(table) when we insert the data into the same, it is getting replicated in all the 5 nodes even though RF=3.

Shouldn't the data be replicated to 3 nodes only and thus be accessible in 3 nodes and not all 5 nodes?

Also, we have used vnodes.


texts: Transition to innodb_file_per_table for 30+ databases

texts:
Currently I have a MySQL Server with innodb_file_per_table = 0
Based on a number of factors, it seems to make sense for me to change that to innodb_file_per_table = 1
I understand that to make this all work I need to

Use mysqldump to export all databases into individual files
Shutdown mysql
Change the innodb_file_per_table setting
Restart mysql
Import each database

Question is, can I do the following
Day #1

Shutdown mysql
Change the innodb_file_per_table setting
Restart mysql
Export some databases
Import those databases back into mysql

Day #2

Export some databases
Import those databases back into mysql

Until I've exported an re-imported all of the databases?
I'm assuming that during the transition some databases will continue to use the ibdata1 file while the exported and re-imported database will use their own datastores.  Will having a mix of datastores cause issues or am I OK doing a phased transition?


texts: Installing Oracle 19c server on Windows 7

texts:
I was having a problem after migrating my Database from 11.2.0.4 to 19c using dbua.
The migration process using dbua is smooth and successful except for migrating the TNS listener. I finally migrate the database without the listener and aims to create another listener after the migration.
I followed the instructions in "Net Configuration Assistant" to create a listener with the default port (1521). The listener is created successfully.
When I tried to start the newly created listener service, the following error is shown in the log,
Error listening on: (ADDRESS=(PROTOCOL=ipc))
TNS-01191: Failed to initialize the local OS authentication subsystem
 TNS-12557: TNS:protocol adapter not loadable
  TNS-12560: TNS:protocol adapter error
   TNS-00527: Protocol Adapter not loadable

I did some research about the error TNS-01191 and I had already updated the environment variables "PATH" with the new Oracle home bin directory.
May I know if anyone had installed Oracle 19c on Windows 7 and configured the TNS listener successfully?
FYI, the tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

The listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENERFORSD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = <ORACLE_HOME_MASKING>)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:<ORACLE_HOME_MASKING>\bin\oraclr19.dll")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = <ORACLE_HOME_MASKING>)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:<ORACLE_HOME_MASKING>\bin\oraclr19.dll")
    )
  )

<LISTENER_NAME_MASKING> =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <HOSTNAME_MASKING>)(PORT = 1521))
    )
  )


texts: SQL Server Replication Agent Profile -Output Parameter

texts:
I'm trying to add the -Output parameter to my log reader agent for transactional replication and getting this error:

Msg 21805, Level 16, State 1, Procedure sp_MSvalidate_agent_parameter, Line 92
The agent profile creation process cannot validate the specified agent parameter value.
'K:\Inst06_TempDB\MSSQL10.INST06\MSSQL\Log\ReplOutput-LogReaderAgent.txt' is not a valid
value for the '-Output' parameter. The value must be an integer. Verify that replication
is installed properly and that sp_add_agent_parameter is invoked with a valid value.

http://msdn.microsoft.com/en-us/library/ms146878(v=SQL.100).aspx shows that -Output expects a path, not an integer as the error message says.

Pub & Sub are both v9.0.4211, Dist is v10.0.2723

My Script (run at distributor):

EXEC sp_change_agent_parameter @profile_id = 18
                              ,@parameter_name = '-Output'
                              ,@parameter_value = 'K:\Inst06_TempDB\MSSQL10.INST06\MSSQL\Log\ReplOutput-LogReaderAgent.txt'
EXEC sp_change_agent_parameter @profile_id = 18
                              ,@parameter_name = '-OutputVerboseLevel'
                              ,@parameter_value = '2' --[0|1|2|3|4] default 2


texts: How can I delete a corrupted table space?

texts:
Problem:
On an Oracle 11.2g DB;
We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager).

What can we do?
What other information is needed to diagnose the problem?

(We've meanwhile checked the file system, which is ok, but the DBF file for the tablespace is lost.)


texts: Percona 5.7.24 : How to fix disk slave storage full

texts:
I find that my slave disk is full, but I don't understand what makes it full.
Can anyone help me explain what this file is?
And how it is formed, so that it makes lots of files that make storage full.
Check in mysql error log I did not find anything
Some file types that make my disk full like this:


texts: Could someone explain bizarre behavior executing millions of UPDATES?

texts:
Could someone explain this behavior to me? I ran the following query on Postgres 9.3 running natively on OS X. I was trying to simulate some behavior where the index size could grow much larger than the table size, and instead found something even more bizarre.

CREATE TABLE test(id int);
CREATE INDEX test_idx ON test(id);

CREATE FUNCTION test_index(batch_size integer, total_batches integer) RETURNS void AS $$
DECLARE
  current_id integer := 1;
BEGIN
FOR i IN 1..total_batches LOOP
  INSERT INTO test VALUES (current_id);
  FOR j IN 1..batch_size LOOP
    UPDATE test SET id = current_id + 1 WHERE id = current_id;
    current_id := current_id + 1;
  END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT test_index(500, 10000);

I let this run for about an hour on my local machine, before I started getting disk issue warnings from OS X. I noticed that Postgres was sucking up about 10MB/s from my local disk, and that the Postgres database was consuming a grand total of 30GB from my machine. I ended up cancelling the query. Regardless, Postgres did not return the disk space to me and I queried the database for usage statistics with the following result:

test=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

           relation            |    size
-------------------------------+------------
 public.test                   | 17 GB
 public.test_idx               | 14 GB

However, selecting from the table yielded no results.

test=# select * from test limit 1;
 id
----
(0 rows)

Running 10000 batches of 500 is 5,000,000 rows, which should yield a pretty small table/index size (on the scale of MB). I suspect that Postgres is creating a new version of the table/index for each INSERT/UPDATE that's happening with function, but this seems strange. The entire function is run transactionally, and the table was empty to start.

Any thoughts on why I'm seeing this behavior?

Specifically, the two questions I have are: why has this space not yet been reclaimed by the database and the second is why did the database require this much space in the first place? 30GB seems like a lot even when accounting for MVCC


texts: MS SQL Server autogrow configuration with heavy loaded database

texts:
I have a database its size around 200G and it is heavy loaded with many inserts happens every day (it is grow around 3 to 4 GB every day), but the Auto-Grow setting is set to 1 MB.

I think this is a problem and I think if I set it to 1GB it will increase the performance and capability, is that right?

I think with 1MB auto-grow the database will be always busy to allocate a new space, is that right ?
Any suggestion for best practical way to increase the performance?

Thanks all


texts: member access to couchDB browser gui fauxton

texts:
I'm using Couchdb v3.0.0, and testing the browser GUI.

I have created users in the database "_users", and have also assigned a role to that user.

I have tested by getting related documents via curl or browser with that user on a particular database with no problems.

But if I want to login to the browser GUI with that user, it will just keep loading in the "databases" section. Choosing other sections will also be prompted "You are not a server admin" in 1 step or 2.

The above happens regardless if the tester account has been assigned member or admin access, neither of them can use the browser GUI to browser documents inside the database.

Is it true that the browser GUI is only usable by admins (which are defined in the local ini files)? or am I missing anything?


texts: I have a file which I need to look at for errors, I got it from someone its extension is mysql.log.3.gz and when I uncompressed it, it has mysql.log.3, It is a log file? And if I open it in text editor like sublime text. It’s a huge file. But how do I really make use of this file so it is useful to clear out errors?


texts:
I have a set of ids that I extract using a join of 2 tables.
Now I need some information from 2 other tables. What would normally be the best approach?
So a SELECT IN using the set of ids I already have, against each of the other 2 tables OR
Should I try to join these tables to the first 2 I originally do to get the set of IDs?
Is there a standard practice?


texts: SQL Server AG Listener issues in an AWS VPC

texts:
I'm experimenting with creating a 2-node SQL Server Availability Group following Guy Glantser's excellent guide at MadeiraData where he describes a configuration with no AD domain or internal DNS.  Note that I'm building everything on AWS EC2 instances.  Everything is working well (including the certificates and logins, the AG is fine and can fail over successfully) however I cannot get the AG Listener to function properly.

Guy's instructions do not mention adding a reference to the Listener in the HOSTS files, however, I have done so.

I can connect to the Listener via the name OR its IP address ONLY from whichever is the Primary AG node.  I cannot connect to the Listener (on an unused IP address and listening on port 1433) from another node or another machine - timeout.  When I failover the AG, I can then only connect to the Listener on the "current" Primary, and the "former" Primary cannot reach it anymore.  I've been testing the connections with SSMS, UDL files and telnet to port 1433.

Am I missing something fundamental about AGs, or as I suspect, am I running into an issue with the Listener and DNS/IP addressing inside an AWS VPC?

I'm running Windows Server 2016 Datacenter edition AMI instances and SQL Server 2016 Evaluation Edition SP1.


texts: How to query readonly replica in Always On cluster

texts:
We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would be a somewhat search-server.

I do know how to set ConnectionString in the C# so it will use SECONDARY replica - just add ApplicationIntent=ReadOnly.

But I don't understand how to address the SECONDARY from stored procedure if I'm using distributed query. Is there a way to set some parameters to the query (like WITH statements or something), so query would use only replica and not the PRIMARY node?

The thing is, obviously from the start server1 is PRIMARY and server2 is SECONDARY, but when server1 fails then server2 is PRIMARY and server1 will be SECONDARY after it get fixed. So I cannot just use the static server2's name.

So far I've managed to get current replica's server name as a variable and use it in EXEC:

-- find first available replica
DECLARE @replicaServer nvarchar(50)

SELECT TOP 1
    @replicaServer = RCS.replica_server_name
FROM
    sys.availability_groups_cluster AS AGC
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
          ON RCS.group_id = AGC.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
          ON ARS.replica_id = RCS.replica_id
    INNER JOIN sys.availability_group_listeners AS AGL
          ON AGL.group_id = ARS.group_id
WHERE ARS.role_desc = 'SECONDARY'
      AND connected_state = 1

-- and query it
DECLARE @cmd nvarchar(MAX) = 'SELECT * FROM [' + @replicaServer + '].[somebase].[someschema].[sometable]'
EXEC (@cmd)

But that's a shame, I guess.


texts:
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are experiencing a peculiar trend whilst processing.

SQL Server process requests at a blistering rate for 1 minute, followed by ~20 seconds of increased disk write activity. The following graph illustrates the problem.

Yellow = Transactions per second
Blue   = Total CPU usage
Red    = Sqlsrv Disk Write Bytes/s
Green  = Sqlsrv Disk Read Bytes/s

During troubleshooting, we tried the following without any significant change in the pattern:

Stopped SQL Server Agent.
Killed almost every other running process (No A/V, SSMS, VS, Windows Explorer, etc.)
Removed all other databases.
Disabled all conversation timers (we don't use any triggers).
Moved away from a message queue driven approach to a simple/crude table monitoring design.
Used different loads from light to heavy.
Fixed all deadlocks.

It seems as if SQL Server might be building up its cache and writing it to disk at specific time-based intervals, but I can't find anything online to support this theory.

Next, I plan to move the solution over to our dedicated test environment to see if I can replicate the problem. Any help in the interim would be greatly appreciated.

Update 1
As requested, herewith a graph that includes the Checkpoint Pages/Sec, Page Life Expectancy, and some disk latency counters.

It appears as if the Checkpoint (light blue line) is the cause of the reduced performance (yellow line) we're observing.^

The disk latency remains relatively consistent during processing and the page life expectancy does not seem to have any noticeable effect. We also adjusted the amount of ram available for SQL Server, which also did not have a big effect. Changing the recovery model from SIMPLE to FULL also made little difference.

Update 2
By changing the "Recovery Interval" as follows, we've managed to reduce the interval at which checkpoints occur:

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE
GO

EXEC sp_configure 'recovery interval', '30'
GO

RECONFIGURE
GO

EXEC sp_configure 'show advanced options',0
GO
RECONFIGURE

I am unsure whether this is bad practice though?


texts:
I am currently a College Student and am taking an introduction to Oracle 10G. I cannot seem to make it to class all of the time and am curious to ask if there is any Virtual Oracle Server or something along those lines that would allow me to have a (in a nutshell) Virtual Database in which I can connect to via SQL* Plus and enter commands that I am learning? I am running Windows 7 Home Premium.

Thank you so much!


texts: Using pg_dump to create 144 backups per day

texts:
Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is).

Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In other words: 144 times per day.

The goal is to ensure that data which is deleted from the original "live" database is maintained in the backups. All deleted data is preserved in the backups. Since the data is deleted regularly, there needs to be backups every 10 minutes.

Would the cron job calling pg_dump create 144 individual backups?

Can these 144 backups be merged together after every 24 hour period and information extracted with SELECT?

The issue also is that one might only want to check the data in a specific table yet the whole database is backed up.

What would be the best way to achieve this?

Thank you.


texts: Are multiple column index used in single column where clause

texts:
I have a table with a defined non clustered index on 3 columns.
CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC
)

Will it be used if i do a request with a where clause on a single column or do I need to create a new index on the specified column?
e.g. :
SELECT TOP 1000 * from MyTable WHERE Col2 in (1,2,3)


texts:
How can we configure Ola Hallengren's solution on a managed instance? (We want output file for each run, storing it is an issue)
If we want backup retention longer than 35 days for the database in managed instance, what are the possibilities? (copy_only backups to blob storage?)


texts: Using Central Management Server to provide convincing performance statistics

texts:
Central Management Server

The company I work offers dedicated VMware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance SQL Servers ranging in versions from 2000 to 2008. Among them you will find 40 Express editions, 10 Standard editions and several MSDE and workgroup editions.

Via a shiny new SQL Server 2008 R2 Standard edition configured as the Central Management Server, I can access every one of these remote machines simultaneously.  Each server is registered in a group based on its version, 8, 9 or 10.  This means that I can query each group of servers based on their version to avoid compatibility errors.

The problem

Normally, we can only sell the idea of increasing RAM, CPUs or moving SQL Server to a dedicated machine because the customer asked us to do so for N reasons.

The goal

I would like to use Central Management Server to go one step further by going to our customers and saying, if you did "this" then your website or application would be faster times X. Imagine an ASP web page that takes 3 seconds to load, by increasing the RAM, in other words by what the buffer pool really needs, that same page would load in 1 second.

I found this line of T-SQL code from Brent Ozar's Take Over an Unknown Server

    SELECT  @@SERVERNAME AS ServerName ,
            YEAR(backup_finish_date) AS backup_year ,
            MONTH(backup_finish_date) AS backup_month ,
            CAST(AVG(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_avg ,
            CAST(MIN(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_min ,
            CAST(MAX(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_max
    FROM    msdb.dbo.backupset bset
    WHERE   bset.type = 'D' /* full backups only */
            AND bset.backup_size > 5368709120 /* 5GB or larger */
            AND DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) > 1 /* backups lasting over a second */
    GROUP BY YEAR(backup_finish_date) ,
            MONTH(backup_finish_date)
    ORDER BY @@SERVERNAME ,
            YEAR(backup_finish_date) DESC ,
            MONTH(backup_finish_date) DESC
GO

This provides the kind of information I'm looking for.

Questions

The queries I've been using show general statistics about things like differences between the target and total memory, basic wait stats and surface area issues.  That's great if you're in IT, but not very helpful or convincing for the guy who only cares about his web page or report opening up 2 seconds faster.

How can I, using Central Management Server, provide convincing performance related information for the individual servers managed with it?
After finding interesting servers thanks to CMS, what 3rd party software do you recommend that is capable of providing decent performance graphs/charts without having to add anything to the client server or modify it in any way. Keep in mind, these servers always belong to someone else.  We only have the right to monitor.
Question about security.  I am using a windows login called dba who only has VIEW SERVER STATE for SQL Server 2005 and 2008.  Is that safe?  For SQL Server 2000, the user is simply able to log into SQL Server.  Should I restrict his access in any way?


texts: Design read and write user permissions on an entity

texts:
I would like to enforce user permission to read and write folder and file entities.
By read I mean SELECT and by write I mean INSERT or UPDATE.
I'm not sure how to model this,and would like to learn what is the appropriate way to achieve the requirements as follow:
A company should have (or not) permission to read folders and the files related to it.
A user should have (or not) permission to read the folder his company has permission on and have (or not) permission to write file.
Meaning reading is a permission in a company scale and writing is a permission in a user scale.
These are the entities I currently have:
 CREATE TABLE "company" (
                "id" SERIAL PRIMARY KEY NOT NULL,
                "name" varchar (50) NOT NULL
            )

 CREATE TABLE "user" (
                "id" SERIAL PRIMARY KEY NOT NULL,
                "company_id" integer NOT NULL REFERENCES "company"(id),
                "full_name" varchar (50) NOT NULL,
                "email" varchar (50) UNIQUE NOT NULL,
                "password_hash" varchar NOT NULL
            )

 CREATE TABLE "project" (
                "id" SERIAL PRIMARY KEY NOT NULL,
                "name" varchar (50) NOT NULL
             )

 CREATE TABLE "folder" (
                "id" SERIAL PRIMARY KEY NOT NULL,
                "name" varchar (50) NOT NULL,
                "project_id" NOT NULL REFERENCES "project"(id)
             )
 CREATE TABLE "file" (
                "id" SERIAL PRIMARY KEY NOT NULL,
                "name" varchar (50) NOT NULL,
                "folder_id" NOT NULL REFERENCES "folder"(id)
             )

 CREATE TABLE "project_company" (
                "id" SERIAL NOT NULL,
                "project_id" integer NOT NULL REFERENCES "project"(id),
                "company_id" integer NOT NULL REFERENCES "company"(id),
                 PRIMARY KEY (project_id, company_id)
             )


texts: How to model data which requires index on all columns?

texts:
I have following use case where there are bunch of IDs (GUID) which are mapped to each other. I need to lookup all the IDs given any single ID. For example, let's say my data has following columns:

uid, id1, id2, id3, id4, id5

How to support the following queries:

1. SELECT uid FROM table WHERE id1=x;
2. SELECT id1, id2, id3, id4 FROM table WHERE uid=xyz;

The number of IDs can change (I have to add new columns).

I can create indexes on all columns but that would not be very effective. Should I model this data differently?
Is there any NoSQL database which can help model this use-case?

To give more business context for clarity, we have data coming from multiple data sources which have their own custom_id. But they all identify the same person (UID). Thats why we need to map all the data source ids to our own internal UID. Hence the question.

The number of columns have max bound of 20-25.
They are nullable.
The number of id's can be different per row (thats why nullable).
Datatypes are STRING for all.
UID will be primary key.


texts:
I have the following SQL:

UPDATE table_name as t
  set value = c.value
from (values(10, 'key_1', 60),(11, 'key_2', 10)) as c(id, key, value)
where t.id = c.id and t.key = c.key

This works fine only in the case if the given 'id' and 'key' already exist in the table. However I need to make it UPDATE the value if 'id' and 'key' exist in the DB and INSERT a new row with the given 'id', 'key' and 'value' if it does not.

I got this example working as an UPSERT but for one set of values only:

WITH upsert AS (
 UPDATE table_name
   SET value=80
 WHERE id=10
   AND key='key_1' RETURNING *
)
INSERT INTO table_name (id, key, value)
SELECT 10, 'key_1', 10
WHERE NOT EXISTS (SELECT * FROM upsert);

I'm not sure how to incorporate this into my case to work with multiple values (first example with UPDATE).

I do not have a unique constraint or index on (id, "key")


texts: I need to audit all actions for a user on all databases - not sure best way

texts:
I have a user on a SQL Server that is used for executing SSRS queries. This user was made a sysadmin before I started at this job. I want to remove the sysadmin permission, but not break any reports. I do not know what DDL/DML the user is actually performing.

With that said, I want to run an audit, which I have done before, but since the auditing is so fine grained, I can't wrap my head around what all I should audit to catch the user actions on all databases.

Can anyone shed some light?

Thanks in advance!


texts:
Can I specify a specific database to be backed up first and another database after that?

For example in the following order:

DB2
DB3
DB1

The code which I am currently using is as follows:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES',
    @Directory = N'E:\MSSQL\Backup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @CleanupTime = 180,
    @Compress='Y',
    @Encrypt = 'Y',
    @EncryptionAlgorithm = 'AES_256',
    @ServerCertificate = 'DBCertificate',
    @CheckSum = 'Y',
    @CleanupMode = 'BEFORE_BACKUP',
    @LogToTable = 'Y'" -b


texts: Dynamic Partition Elimination when using "OR " or "IN"

texts:
Say I have a query like this:

SET @ContainerId = 5

SELECT  *
FROM    Container.Container_Partitioned
WHERE   containerId = @ContainerId

When I run this, I get a query plan with Actual Partition Count equal to 1.  (Good)

But if I run this:

SET @ContainerId1 = 5
SET @ContainerId2 = 30000000

SELECT  *
FROM    Container.Container_Partitioned
WHERE   containerId = @ContainerId1 OR ContainerId = @ContainerId2

I get an Actual Partition Count equal to all my partitions. (Bad)

I have two questions:

Why can the optimizer figure it out with one parameter, but not with two?
I guess it is the "OR" operator.  But why?
How can I get it to correctly see that only two partitions are need without using  OPTION(RECOMPILE) or something similar?
I can recompile each time if I have to, but I would rather not for obvious reasons.


texts: Deleting rows from a table with a very small amount of expansion space

texts:
I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time.  I have tried running the following:

WHILE 1 = 1
BEGIN
    DELETE TOP ( 4000 )
    FROM    [OLD_TABLE] WITH (TABLOCKX)
    WHERE   [Date] < '2014-01-01 00:00:00'
    IF @@ROWCOUNT = 0
        BREAK
END

this seems to be working, but VERY SLOWLY...   like 17+ hours, and only ~140,000 rows deleted.

The kicker here is this table and the others that need to be reduced are very large, around 50-60 million rows, and ~16-17 GB of space.  There is only 20 GB of space left on the physical disk.  I have looked at putting the data that I want to keep in an intermediate table, truncating and repopulating, but I am afraid that we will run out of space on the disk while these operations are completing.  This is a production database.  I have inherited this mess and I am trying to clean this up and am trying to get this DB cleaned up so we can stay functional until we can get new hardware in.

Are there any methods that I (a relative newcomer to database management) have not come across that may allow me to do this in a faster, more efficient manner?

*The table and column names have been anonymized.

EDIT


texts: Find entire hierarchy based in all hierarchy levels

texts:
I have a table that looks like this:

Where EMP has a child SEG and it has a children SUP and so on... What I would like to do is find the full hierarchy EMP>SEG>SUP>DIR>REG>GOP in any of the children based in something like 'where' clause (something like: 'where inferior_codigo = SUP0001'), they should return me all the hierarchy of this particular item.

I did something like this but i can only find the parent or children, not both at the same time, with something like this:

   SELECT
s1.inferior_codigo AS lv1,
s2.inferior_codigo AS lv2,
s3.inferior_codigo AS lv3,
s4.inferior_codigo AS lv4,
s5.inferior_codigo AS lv5,
s6.inferior_codigo AS lv6
FROM subs AS s1
LEFT JOIN subs AS s2 ON s2.superior_codigo = s1.inferior_codigo
LEFT JOIN subs AS s3 ON s3.superior_codigo = s2.inferior_codigo
LEFT JOIN subs AS s4 ON s4.superior_codigo = s3.inferior_codigo
LEFT JOIN subs AS s5 ON s5.superior_codigo = s4.inferior_codigo
LEFT JOIN subs AS s6 ON s6.superior_codigo = s5.inferior_codigo

WHERE s1.superior_codigo = 'EMP0001' LIMIT 25;

Which will result in the following:

Can someone tell me what I should do to accomplish this? Thanks in advance :)


texts: How to use container to do database high availability?

texts:
About traditional database high availability, the method is like:

master-master replication
master-slave replication
automatic failover

If use container to do that, we can use these ways:

Use busybox to save data, but how to do when the data goes large if only use container?
Use volume feature to sync data from container to hard disk. This way can do replication as traditional way
Connect database from application container. Then the next thing is the same as traditional way
Use large storage service like OpenStack Swift or AWS EBS. Maybe this way can save the data well depend on third party services.

At least, the first choice, use pure container technology, is there a way to do high availability?


texts: The transaction log for the database fills up overnight everyday

texts: Lately my server is overloaded because of daily running SQL jobs, my transaction log grows very large throughout the night and I run out of disc space.  The short term solution was to shrink the log every hour, because even at every 4 hours it seemed to still fill up the whole server (as the shrink job might get blocked from running by other scripts).  Is there any long term solution to this so I can go back to backing up/shrinking the transaction log once per day?


texts: In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

texts:
On Windows, I upgraded from 5.1 to 5.5 no problem.

Copied my 5.1 data folder into my 5.5 instance
Started mysqld skipping grants
Ran mysql_upgrade

All good, but going from 5.5 to 5.6:

Copied 5.5 data folder to 5.6 instance
Started mysqld skipping grants
Ran mysql_upgrade

but I get:

C:\Users\QAdmin>mysql_upgrade
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
Running 'mysqlcheck' with connection arguments: "--port=3306"
mysql.user_info                                    OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: "--port=3306"
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe: Got error: 1130: Host 'localhost' is not allowed to connect to this MySQL server when trying to connect
FATAL ERROR: Upgrade failed

If I look at the mysql.user table it is completely empty.

Has anyone seen this or know what is going on?
During the "upgrade"  the user table gets erased and when it tries to connect it can't?

Thanks.


texts: SELECT SERVERPROPERTY('ProductUpdateLevel') SHOWING NULL

texts:
Good Morning Experts,

I had applied CU7(cumulative update 7) to SQL Server 2012 EE SP1 instance. After applying, when i queried SELECT SERVERPROPERTY('ProductUpdateLevel'), it is displaying as NULL instead of CU7. Could you please help.


texts:
I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.

Let's have a table with 5 fields.

Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}

Dependencies are this way:

StudentName, SubjectCode, #Exam -> Grade

SubjectCode -> SubjectName

SubjectName -> SubjectCode

Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.

Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade

According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

Gradings = {StudentName, Subject Code, #Exam, Grade}

Subjects = {Subject Code, SubjectName}

But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.

I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".

What am I doing wrong?


texts: When is transaction removed from sys.dm_tran_active_transactions?

texts:
We are having some issues with clients getting Transaction Timeout when connecting to our clustered SQL Server 2008 R2.

To investigate the problems I have been digging into sys.dm_tran_active_transactions and a few times I saw rolled back transactions being there up til 4 hours after they were rolled back(After four hours the IIS webserver that started the transaction were restarted and they dissappeared), this was non-DTC transactions even though we do have DTC transactions as well.

So when can I expect committed/rolledback transactions to be cleared out of sys.dm_tran_active_transactions and if it is supposed to happen immediately what kind of issues could keep them "hanging" there.


texts:
Does varchar and Text affect the database size?
Also, do they differ in speed?

I have a Database that will have at least 4000 to 8000 new rows per day.
I'm quite worried about how it performs. This is my first time setting up an Enterprise-grade system and database though.


texts:
I have been having failures to install SQL Server 2012 SP2 on a 2-node active\passive cluster. I have uploaded detail and summary logs for the latest run on the passive node here:
https://www.dropbox.com/s/4tlmv3hmzvbb9l4/Log%20Files.7z?dl=0

Due to not being able to pinpoint the error I have tried the following:

rebooting and running the patching from the GUI and command line with and without pre-extracting the files.
I have also ensured cluster services and SQL role are on the same active node, and brought the Available Storage group online although it does not have any unassigned disks in.
I have also ensured all default directories are present on the shared disks, and checked the registry for any features which are pending an update. I did not find any as I had seen reference to these problems.
I have also tried using 2 different admin accounts to run the setup even though I could not see any evidence of this problem

The GUI mimics the detail log showing all verification successful, the patching start; whereby after the patching begins it then ends with no error message. A completely blank feature list box is shown and a blank text box for log file locations, although these are obviously generated in the default location.

It seems to me the patching starts, but fails before any features begin to update without a specific error.

Any guidance is greatly appreciated.


texts: MariaDB - cannot set max_connections and wait_timeout through my.cnf

texts:
I am struggling to set max_connections and wait_timeout parameters in /etc/my.cnf but MariaDB does not seem to read the parameters from the file (it reads some of the others, I haven't checked all).

My /etc/my.cnf file:

[mysqld]
#skip-grant-tables
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# network
connect_timeout = 61
wait_timeout = 86400
max_connections = 100000
max_allowed_packet = 64M
max_connect_errors = 1000

# limits
tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 512

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[client]
port = 3306
socket= /data/mysql/mysql.sock

But when I check max_connections and wait_timeout variables in MariaDB, it shows the default value:

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

However, other parameters in my.cnf are correct:

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
+--------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 61    |
+-----------------+-------+
1 row in set (0.00 sec)

I can set max_connections variable from mysql command line but it resets itself when I restart the service:

MariaDB [(none)]> set global max_connections := 10000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10000 |
+-----------------+-------+
1 row in set (0.00 sec)

Hower, for wait_timeout this does not work:

MariaDB [(none)]> set global wait_timeout = 86400;                                                                            Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';                                                                         +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

wait timeout is set as a session variable:

MariaDB [(none)]> SELECT * FROM information_schema.global_variables WHERE variable_name='wait_timeout' UNION SELECT * FROM information_schema.session_variables WHERE variable_name='wait_timeout';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| WAIT_TIMEOUT  | 86400          |
| WAIT_TIMEOUT  | 28800          |
+---------------+----------------+
2 rows in set (0.00 sec)

Checked all my.cnf files, none of the is overriding the parameters:

[/] # locate my.cnf
/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf~
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf
/root/.my.cnf

OS: RHEL 7

MariaDB version: mariadb-server-5.5.47-1.el7_2.x86_64


texts: dbms_utility.get_dependency give me error "ORDER SIBLINGS BY clause not allowed here" , why?

texts:
Oracle 9.2.0.8 running at linux .

I'm trying identify the dependencies of one object with the dbms_utility.get_dependency command.
But unfortunately it always return the error ORA-30929...
I already looked at Oracle Support, googling... found nothing .

No matter the user, either if I run with DBA user, SYS user or the owner... always get into the same problem.

Anyone have any ideas what is this problem?
How to solve or workaround?

Error starting at line : 1 in command -
begin
  DBMS_UTILITY.GET_DEPENDENCY('TABLE','CONT','LANC');
end;
Error report -
ORA-30929: ORDER SIBLINGS BY clause not allowed here
ORA-06512: at "SYS.DBMS_UTILITY", line 844
ORA-06512: at line 2
30929. 00000 -  "ORDER SIBLINGS BY clause not allowed here"
*Cause:    ORDER SIBLINGS BY clause is specifed in a query which doesn't
           have CONNECT BY clause.
*Action:   Remove ORDER SIBLINGS BY clause or add CONNECT BY clause.


texts:
I'd like to optimize (lower) the InnoDB buffer pool size. How can one find out how many distinct pages are active in the InnoDB buffer pool? A page could be made young twice in a while (see SHOW INNODB ENGINE STATUS), so one can't find out how many distinct pages are actually active in the buffer pool.

I have Googled around and read various "how to set/find out optimal innodb buffer size" type Q&As, but they don't involve that question.


texts: Query with conditional JOIN running slow

texts:
I have a query the first part of which looks like this -

With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    OR
                    B.col3= 'Y' and B.col4=A.col4
                    OR
                    .
                    .
                    .
                    .
                    )

)

Now this query runs for more than 4 minutes and the execution plan showed a table spool which was data size of 600 MB and looked like this -

I changed this to conditional JOIN to UNIONs -

With CTE AS (
select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    )
    UNION
    select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'Y' and B.col4=A.col4
                    )
     UNION
     .
     .
     .

 )

Now the spool with the large number of rows vanished and the run time reduced to 17 seconds. However when I check the IO statistics, the logical reads are higher than the previous slower query. What could the reason be ?


texts: How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)


texts:
Oracle parallel explain plans have a column named IN-OUT, and it often contains values P->S, PCWP, and PCWC. What do these values mean?

Here is an example explain plan with an IN-OUT column:

--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN             |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,02 | PCWP |            |
|   5 |      PX SEND BROADCAST   | :TQ10000 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR  |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL | T3       |  Q1,00 | PCWP |            |
|*  8 |     HASH JOIN            |          |  Q1,02 | PCWP |            |
|   9 |      PX RECEIVE          |          |  Q1,02 | PCWP |            |
|  10 |       PX SEND BROADCAST  | :TQ10001 |  Q1,01 | P->P | BROADCAST  |
|  11 |        PX BLOCK ITERATOR |          |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL| T1       |  Q1,01 | PCWP |            |
|  13 |      PX BLOCK ITERATOR   |          |  Q1,02 | PCWC |            |
|  14 |       TABLE ACCESS FULL  | T1       |  Q1,02 | PCWP |            |
--------------------------------------------------------------------------


texts: What am I missing for my SSRS Service account local server permissions?

texts:
I have a relatively new instance on SSRS 2008R2 SP3 running on Windows Server 2008 R2. I am trying to set up separate domain service accounts for the separate SQL Services i.e. agent, engine, reporting, etc. I have done this successfully for the Agent and Engine services using simple Domain accounts and following the required permissions from docs.microsoft.com.

At first I had made local accounts for all services because of a video I watched that used that as an example. So now that I actually wanted to start using reporting services I switch the account to a newly created Domain account. Using the pertinent section in the above article I configured, via GPO, the account to have "Log On As A Service" right. I ensured that right is present in the groups I created.

I then went into the Reporting Services Configuration Manager to change the account which was successful except that the step for starting the service ended in error.

System.InvalidOperationException: Cannot start service ReportServer on computer 'SQLSERVER01'. ---> System.ComponentModel.Win32Exception: Access is denied
   --- End of inner exception stack trace ---
   at System.ServiceProcess.ServiceController.Start(String[] args)
   at System.ServiceProcess.ServiceController.Start()
   at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)

Event Viewer has a similar error which I include to save the questions.

The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:
Access is denied.

So this is a Windows permission issue but I don't see what the problem could be since the Log On As a Service permissions are set correctly. That is verified by the security logs that show a successful logon of the account. If I make my service account a local admin on the server then the service starts correctly but I should not have to do that.


texts: What kind of checks should I perform while dealing with stored procedures?

texts:
CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end

or

    CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end

Which one of these queries is considered better (from different aspects) and why? sometimes I feel like doing some check before making the update is not necessary, like in this case for example.

First one will check If the account is unlocked, If yes, it locks it.

While the second one will lock it regardless of the current state of the account.

So, for the user who is using the application that execute this procedure, does it really matter?


texts: Return multiple columns (int datatype) inside a CASE expression

texts:
How can I get multiple columns from a CASE expression?

with cte
as
(
  select
    ROW_NUMBER() over (order by id)'Rownumber',
    id
  from
    dbo.Test
)
select
  case
    when (b.id-a.id) > 1 then a.id
    else ' '
  end
from
  cte a,
  cte b
where
  b.Rownumber = a.Rownumber+1

In the above query, I want to select a.id,b.id only if (b.id-a.id) > 1.
I tried to do it using a nested CASE expression as well as by UNION, but still not able to get the expected result.


texts: AWS RDS MYSQL How do I reduce the DB size?

texts:
Possible Duplicate:
  How do I reduce the database size on AWS RDS MySQL?

I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much.

How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly wack it!


texts: Query is fast but becomes sluggish when created as a view

texts:
Execution Plan as Query

SELECT VP.Branch
     , VP.ROUTE AS Route
     , VP.SAPCustomerID
     , S.CustomerID
     , S.ProductID
     , S.Date
     -- Group by Customer
     , CustomerQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     , CustomerFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     -- Group by Route
     , SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date) AS RouteQuantity
     , RouteFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date)
     -- Group by Branch
     , BranchQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date)
     , BranchFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date)
FROM vw_SalesByWeek AS S
INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK)
    ON VP.CustomerID = S.CustomerID
    AND VP.DateFrom <= S.Date
    AND VP.DateTo >= S.Date

Execution Plan as View

-- Where vw_SalesByWeekSummary is the query above exactly.
SELECT [Branch]
      ,[Route]
      ,[SAPCustomerID]
      ,[CustomerID]
      ,[ProductID]
      ,[Date]
      ,[CustomerQuantity]
      ,[CustomerFourWeekSalesAvg]
      ,[RouteQuantity]
      ,[RouteFourWeekSalesAvg]
      ,[BranchQuantity]
      ,[BranchFourWeekSalesAvg]
FROM vw_SalesByWeekSummary
WHERE Route = '0600'

Problem

The query alone or as a stored procedure works just fine; however, the query as a view decides to do scans instead of seeks and uses different indexes.
How can I get the query to behave properly as a view? What is causing it to use different indexes and scan instead of seek?

SQLPlan files

Solo Query Plan
View Query Plan

Indexes

-- Solo Query Plan Indexes
CREATE NONCLUSTERED INDEX [IX_VisitPlan_ByRoute] ON [dbo].[SAP_VisitPlan] ([ROUTE] ASC) INCLUDE ([Branch])
CREATE UNIQUE NONCLUSTERED INDEX [UIX_CacheCS_ByWeek] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([ID], [SoldQuantity], [Route], [FourWeekSalesAvg], [NumberOfPriorSalesWeeks])
CREATE NONCLUSTERED INDEX [IX_CacheSS_4wkAvg] ON [dbo].[Cache_ScanSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [Route] ASC) INCLUDE ([FourWeekSalesAvg], [WkStartDate], [SoldQuantity])

-- View Query Plan Indexes
CREATE UNIQUE NONCLUSTERED INDEX [UIX_VisitPlan_PK] ON [dbo].[SAP_VisitPlan] ([SAPCustomerID] ASC, [CustomerID] ASC, [DateTo] DESC, [DateFrom] DESC, [ROUTE] ASC, [DriverNumber] ASC) INCLUDE ([Branch])
CREATE NONCLUSTERED INDEX [IX_CacheCS] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([SoldQuantity], [FourWeekSalesAvg], [Route])
CREATE NONCLUSTERED INDEX [IX_ScanSalesByWeek_Sunday] ON [dbo].[Cache_ScanSalesByWeek] ([WkStartDate] ASC) INCLUDE ([CustomerID], [ProductID], [SoldQuantity], [Route], [FourWeekSalesAvg])