texts: MySQL Router failing with "write error: Resource Temporarily Unavailable"

texts:
I have been using MySQL Router for the past few months without any issues, but I just ran apt-get upgrade on my Linux server, and mysql-router was upgraded from version 8.0.21 up to 8.0.22.
Since then, my connections are hanging when I try to pull back more than about 50 rows of data.
When I look in the mysqlrouter.log file, it show a series of DEBUG messages like this:
2020-10-30 12:55:14 routing DEBUG [7f259745a700] fd=12 write error: Resource temporarily unavailable

Incidentally, I have now uninstalled and re-installed the previous version and it's working as expected.


texts: pg_dump -> import traject using "upsert" paradigm?

texts:
Well say I have two physically separate database and I wish to replace database B with data from A. Except that I wish to keep the data in B which does not exist in A?
So basically I'd go over each entry in A and use an insert-or-replace like:
INSERT INTO table_name (name, value)
VALUES ('hello', 'world')
ON CONFLICT (name) do nothing;

IE the following databases would be merged into the third:
A)
name | value
A    | 1
B    | 2

B)
name | value
B    | 10
C    | 20

MERGED)
name | value
A    | 1
B    | 2
C    | 20

If I use the -c option from pg_dump it would just throw away the "c" value in the merged table.


texts: How to design a database that tracks sports player's positions

texts:
I'm building a database for a sports (ultimate) tracking program. Basically, the "raw data" for the program will be the positions and IDs for all the players on the field at one time. But the way I'm currently doing it seems to me to be overly complicated.

The main table, games, has columns for  7-10 player IDs on each team, matched against the IDs contained in the players table. It also has a column for each player's position on the field. But this creates a table with over 28-40 columns!

I'm fairly new to designing my own database, so I could be missing something basic, but is there a simpler way to do this?

(For what I want to do with the data, I do need to have a record of each player's position at each timestamp)

Clarification: 'position' means physical location on the field, not the name of the player's position


texts: On Oracle 12c. is there any way to enable the audit trail on SELECT on whole schema of some users?

texts:
Is there any way to enable the audit trail on SELECT on whole schema of some users?

I can only see there is example on auditing on certain table in a schema, but is there any way to audit on whole schema?


texts: How trigger update in replica database on Postgres?

texts:
In both servers (master and replica) I have two tables like this:

CREATE TABLE my_table
(
    id SERIAL,
    name TEXT,
    "timestamp" timestamp without time zone,
    CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

In my master server I created a publication with:

CREATE PUBLICATION sync_publication;
ALTER PUBLICATION sync_publication ADD TABLE my_table;

Then I created a use (named syncguy):

CREATE ROLE syncguy WITH REPLICATION LOGIN PASSWORD 'root';

After I granted privileges:

GRANT ALL PRIVILEGES ON DATABASE my_database TO syncguy;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO syncguy;

In postgresql.conf setup wal_level to logical:

wal_level = logical

In postgresql.conf setup wal_level to replica:

wal_level = replica

Then I insert in Master:

insert into public.my_table (id, name, "timestamp") values (
    uuid_generate_v4(),
    'foo',
    current_timestamp
);

Then in both server in my_table i get:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | foo  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

Then I update "name col" in Master to bar value, and I get in both server this:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | bar  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

All of this worked well, my problem is when I create a trigger, I'm using this function and trigger on replica server (I don't use triggers in "master server"):

CREATE FUNCTION public.my_func() RETURNS trigger
    LANGUAGE plpgsql
    AS $$DECLARE
    partition_date TEXT;
    partition TEXT;
BEGIN
    partition_date := to_char(NEW.timestamp, 'YYYY_MM_DD');
    partition := 'messages_' || partition_date;

    EXECUTE 'CREATE TABLE IF NOT EXISTS ' || partition || ' (CONSTRAINT ' || partition || '_pkey UNIQUE (id)) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;';

    RETURN NULL;
END;$$;

ALTER FUNCTION public.my_func() OWNER TO postgres;

CREATE TRIGGER my_trigger BEFORE INSERT ON public.my_table FOR EACH ROW EXECUTE FUNCTION public.my_func();

ALTER TABLE public.my_table ENABLE REPLICA  TRIGGER my_trigger;

So, my objective with this trigger is to partition my replicated data, when I insert in master with trigger in replicate server it's works, is create a table like this messages_2020_02_04

The messages_2020_02_04 contains my replicated data and my_table in replica server is just "referenced", as I said, this works well with INSERT (I tested sending several, all went to the tables generated by the trigger), however when executing "UPDATE" in master the lines are not updated in replica.

Is it possible to work on master updates with replica and triggers in "replica server"?


texts: How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?

texts:
I have 2 scenarios for dynamic pivoting.
Scenario 1:
In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on.
The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query.
Table is DATA_1

User: "image.png"
And expected output would be something like this.
P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed.

Scenario 2:
Here were had the data in one table. Now we have separate table containing the labels and current table with values.
SO in table from scenario one we will focus on id, app_id and value columns only.
Select app_id,id,value from data_1;
The labels will come from another table DATA_Labels in column Header_Name:

So, for labels we would use header_names. And Data_1 and Data_Labels could be joined on basis of app_id.
In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there.
I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.
The solution could be with/without pivot or we can also use collections/arrays if possible. ANything that helps in changing the headers dynamically.


texts: What overhead is there if I MERGE row by row?

texts:
I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users inserting or updating data infrequently.

Now, I already have a stored procedure that I use to update the rows in the source table, one row at a time. It would be much simpler to include the MERGE logic in this stored procedure instead of having a separate stored procedure to move everything at once.

Given the usage, I don't see this being an issue, but I was curious what kind of overhead or problems can arise from using MERGE and HOLDLOCK for rows one at a time.


texts:
I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using the table optimize syntax. Is this also true for myisam tables?

I have a 50 million row table and am deleting about 40k rows a day from it (while also adding in 50k new rows).


texts:
I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before attaching it?

More background information: I have a bunch of SAN backup volumes. I attached them to Windows through iSCSI, and now half the drive letters are messed up. Moreover, I multiple volumes should be mapped to the save drive letter, so I can't restore the correct drive letters.

I know that the files are all there, but I don't know how many and which LDF/NDFs I should be attaching with each MDF.


texts: How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

texts:
I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session.

How do I make this setting stick after stopping and restarting MySQL?

NOTE: I am on version  5.5.19 of MySQL (with Moodle) and I just upgraded from MyISAM to InnoDB and therefore need this new setting.

I have tried adding these two commands (separately) to my.cnf: binlog-format=ROW and binlog_format=ROW and neither work. I have stopped and restarted MySQL every time I changed the my.cnf.

The error I get without this being set is the nefarious error message:

Debug info: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.


texts:
Scenario:

Deleted system files (dbf files); and trying to recover database back to normal using available control files, and some redo logs.
Backup is not available, nor are archived redo log files

System information:
OS - RHEL Server Release 7.5 (Maipo)
Oracle Linux Server Release 7.5 (12c)
How did we land up in this situation:

rm  *.DBF in DBF_FILES/ folder; this deleted system01.dbf,  system02.dbf (one file being of system tablespace and one sysaux)

two instances of oracle were running and we were removing files of other instance

restarted ec2 instance

Current Situation:

database is starting in mounted mode only
I have a AMI backup of the server after the deletion and restart of instance
All data files are intact
Control file is intact
Redo archive logs not available; all we have is some redo logs (thought of using redo logs to re-create the entire thing; in line with what postgreSQL offers with WriteAheadLog); but could not find correct resource maybe. (more on this in approaches taken)
A one year old backup of the same database is available which is working but there have been many changes from that time; unluckily the dev does not have any record of what he changed!

Approaches I've tried:

Tried recovering the folder with extundelete, foremost, testdisk (sysadmin type approaches)
The files removed were available on an old backup and I tried copying the same and starting the database
I also created a trace file from the control file and removed the deleted files from trace file and tried starting the database.
Also tried RECOVER DATAFILE '<empty_file.dbf>' by creating empty files of the same name

Happy to connect over video/audio/screen-share if need be.


texts: sp_grantdbaccess cannot be run in a stored proc owned by DBO?

texts:
Maybe I'm getting this wrong, but...
I have a SQL Server 2000 database with a stored procedure that is owned by DBO. This stored procedure is calling sp_grantdbaccess, which can be run by DBO, according to MS documentation.
and I'm running a Windows user that belongs to a database role that has execute permission on this stored procedure.

If I understand ownership chaining correctly (which I obviously don't), I should be able to run this stored procedure, since it is run with impersonation as DBO.

But alas, it is telling me that the user doesn't have permission to run sp_grantdbaccess.

What can I do to solve this?


texts: Query returns all values instead of the row of the one with the max value

texts:
I have this database
https://pastebin.com/WWRxApWn
I need the row of the product with the greatest unitprice. I did:
SELECT id, MAX(unitprice)
FROM product
GROUP BY id;

Instead, it returned every row in products. If I do this:
SELECT MAX(unitprice)
FROM product;

It returns the greatest price, as expected.
I looked into this (https://stackoverflow.com/questions/18957372/sql-max-product-price) and this (https://stackoverflow.com/questions/12366390/how-to-select-product-that-have-the-maximum-price-of-each-category) thread but I can't figure it out.
Help?


texts:
I copy a collection to another collection. Data is 14GB. I deleted the copied collection. Yet the size of the directory is still 14 GB.

I chose repair but it seems to take forever. Actually inserting the whole data took only 20 minutes and the repair is still running till now.

It's just compacting data.

What happen if I do not repair the database? Will files eventually be compacted?


texts:
Is there a way to have multiple tnsnames.ora locations defined? We currently use a server path like TNS_ADMIN=\\192.168.1.100\infosys\oracle\tnsnames but are in a situation this week where that server had to be taken offline for a couple days.

I've tried stacking locations similar to PATH variable but it doesn't work:

> set TNS_ADMIN=z:\Common\oracle;\\192.168.1.100\infosys\oracle\tnsnames
> mctnsping ora-server

McTnsping Utility by Michel Cadot: Version 2018.08.17 on 18-OCT-2018 11:16:33
Copyright (c) Michel Cadot, 2016-2018. All rights reserved.

*** TNS-03514: Failed to find tnsnames.ora file

Single location is fine:

> set TNS_ADMIN=Z:\Common\oracle
> mctnsping ora-server

Used parameter files:
Z:\Common\oracle\tnsnames.ora
...snip...
Attempting to contact ora-server:1521
OK (201 msec)

How to have an alternate location without touching every client to change TNS_ADMIN?


texts: Unable to SQL => Getting content of last 7 days

texts:
I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format: 2014-08-28 11:11:42

All my queries have been a fail till now. I tried the following https://stackoverflow.com/questions/13266130/how-to-select-last-one-week-data-from-todays-date

I just tried:

SELECT  *
FROM   Table_Name
WHERE  connect_time >= DATEADD(day,-7, GETDATE())

I cannot understand the reason its wrong and I am trying to find the syntax so I can edit it myself but I am getting confused what part of the code is syntax and what is their column names.


texts:
I'm trying to understand what postgresql doing in an issue that I'm having. Our app team wrote a function that runs with a cursor over the results of a query and via the utl_file func they write some columns to a file. I dont understand why, but postgresql write the data into the file in the fs in parts. I mean that it runs the query and it takes time to get back results and when I see that the results back postgresql write to file the data and then suddenly stops for X minutes. After those x minutes it starts again to write the data and it continues that way until its done. The query returns total 100 rows. I want to understand why it stops suddenly. There arent any locks in the database during this operation.

my function looks like that :

func(a,b,c...)

cursor cr for

select ab,c,d,e.....

begin

raise notice - 'starting loop time - %',timeofday();

for cr_record in cr

Raise notice 'print to file - '%',timeofday();

utl_file.write(file,cr_record)

end loop

end

I see the log of the running the next output :

starting loop 16:00

print to file : 16:03:01

print to file : 16:03:01

print to file : 16:03:01

print to file : 16:**03**:01

print to file : 16:**10**:00

print to file : 16:10:00
......

Can somebody explain to me this kind of behavior ? Why is it taking some much time to write and in different minutes after the query already been executed and finished ? Mybe I'm getting from the cursor only part of the rows ?


texts: How to generate PostgreSQL COPY bin files?

texts: My Googlefu is failing me. Are there any code snippets, either in Python or FORTRAN, that shows how to create binary files to be used by the COPY command in PostgreSQL?


texts: Compressed Backup size value is incorrect in msdb

texts:
I am not sure if in am checking this correctly.

But i can see the discrepancies in the value of compressed backup size from msdb.The value of compressed size is different what i see on the drives.

These backups are taken from Litespeed. e.g Litespeed backup compresses the backup of database to 1 TB for 8 TB and msdb shows that value in column as 4 TB.

Is there a way to get the correct information using SQL for third party backups?

Query used:

Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -10

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName,
   msdb.dbo.backupset.database_name,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
   END AS backup_type,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024/1024) AS 'Compress Backup Size in GB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date


texts: MSQL state "closing tables" taking double the time of a process

texts:
I have a delete query that is taking a long time. When I check in processlist that status is showing as "closing tables". It is taking a long time to close tables.

Example:
I run a query and the total time is 1:42 seconds and in that 80-90 seconds are for closing table.

This is happening for all queries like load data , select and insert.

Below is my.cnf settings:

key_buffer_size = 2G
sort_buffer_size = 8M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
join_buffer_size = 2M
bulk_insert_buffer_size = 100M
myisam_sort_buffer_size = 64M

#tmp_table_size = 100M
#max_heap_table_size = 64M
#max_allowed_packet = 64M

table_cache=1024

My meminfo

[root@localhost ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          7862       6686       1175          0         11       4091
-/+ buffers/cache:       2583       5278
Swap:        15998         18      15980

Please tell me what kind of changes I need to do in my.cnf file?


texts:
I need to INSERT INTO a new table (FinalTable) from 2 unrelated tables, but values from Table1 will need to be repeat for every value pulled from Table2.

Example:

Table1                      Table2
AK                          ID1
AL                          ID2
AR                          ID3
AZ                          ID4
...                         ...

FinalTable
Column1       Column2
ID1           AK
ID1           AL
ID1           AR
ID1           AZ
ID2           AK
ID2           AL
ID2           AR
ID2           AZ
...

Is it possible to do this without being able to JOIN the two SELECTed tables?


texts: Storing variable length arrays of doubles in MySQL without loss of information

texts:
I am trying to store variable length arrays of C++ doubles into a MySQL database.

I was initially storing each value in the array as a row of the form:

`pv_name` varchar(60) NOT NULL,
`time_stamp` bigint(20) UNSIGNED NOT NULL,
`array_index` bigint(20) UNSIGNED NOT NULL,
`value` double NOT NULL,
`alarm_status_id` tinyint(3) UNSIGNED NOT NULL,
`alarm_severity_id` tinyint(3) UNSIGNED NOT NULL

The pv_name and time_stamp uniquely identified each array. The array_index identified each element of the array.

The problem that I ran into is that the MySQL double type does not accept a 'nan' value. So my thought was to use a single blob type for the entire array. Would this be recommended? One advantage of doing so is that it allows me to store values of other types (tinyint, smallint, int, float, varchar) in this blob column in the same table, whereas before I needed a different table for each type. If this is appropriate, what is the best way to insert the array into the blob? I am currently casting it to a string using a format string of '%.17f' for each value and then using setString with a prepared statement (from the MySQL C++ connector (libmysqlcppconn)). Is it possible to use the '%g' format string instead without losing information? Is there an altogether different approach that may be more appropriate?


texts: Oracle: How to gather stats in a logical standby database?

texts:
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old ;)

SELECT guard_status FROM v$database;

GUARD_STATUS
------------
STANDBY


texts: Get parent/child or parent/siblings depending on the ID passed

texts:
This is similar to my table:

| id | parent_id | title        |
|----|-----------|--------------|
| 61 | NULL      | title of 61  |
| 62 | 61        | another title|
| 68 | 61        | more text    |
| 75 | 61        | testing 123  |

Let's say I select ID 61. I want this output (all children of parent):

| id | title        |
|----|--------------|
| 62 | another title|
| 68 | more text    |
| 75 | testing 123  |

so, i use the following mysql:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON a2.parent_id = a.id
WHERE a.id = 61

...so far so good...

Let's say I select ID 75, I want this output (parent and siblings):

| id | title        |
|----|--------------|
| 61 | title of 61  |
| 62 | another title|
| 68 | more text    |

so, i use this query:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON COALESCE(a2.parent_id, a2.id) = a.parent_id
WHERE a.id = 75 AND a2.id != 75

Is there a way to create a combination of those 2 queries so whatever ID i pass, it will output correctly...

NOTE:
English is not my first language, please feel free to ask questions if its not clear


texts: Is there any automatic built-in mechanism in postgres to backup data if the data have exceeded the maximum permissible storage amount?


texts: Oracle - Foreign Key on non Unique - is materialized view the answer?

texts:
we have the following table to store texts in different languages:

CREATE TABLE TXT
(
  TXTID VARCHAR2(10 BYTE) NOT NULL
, LANGUAGE VARCHAR2(2 BYTE) DEFAULT 'DE' NOT NULL
, STEXT VARCHAR2(10 BYTE)
, LTEXT VARCHAR2(4000 BYTE)
, LABELTEXT VARCHAR2(20 BYTE)
, CONSTRAINT TXT_PK PRIMARY KEY
  (
    TXTID
  , LANGUAGE
  )
)

So you can have multiple language with one txtid.
Now I want to have the TXTID as a foreign key on another table.
First I created a view with

select distinct txtid from txtid

which of course doesn't work because you can't really use the constraints on the view.
So I heard of the materialized view:

CREATE MATERIALIZED VIEW mv_txtid_txt
REFRESH ON DEMAND
AS SELECT distinct txtid from txt

alter table mv_txtid_txt
add CONSTRAINT TXT_PK PRIMARY KEY (TXTID)
ENABLE

So far so fine, not perfect but I thought it would work... adding the foreign key to a table:

ALTER TABLE DATADICT
ADD CONSTRAINT DATADICT_FK2 FOREIGN KEY(TXTID)
REFERENCES mv_txtid_txt(TXTID)
ENABLE

But I get the:
ORA-02270: no matching unique or primary key for this column-list

How to get the not unique TXT.TXTID as a foreign key? Do I really must use triggers on the tables to check the foreign key manually?

Kind regards,


texts: autocommit on long transaction after connection failure

texts:
Is there any spec on what impact a connection failure has on a mutation statement if autocommit is on?

For example, if a client starts, in autocommit mode, a mutation that can take a long time and then goes away, is it defined whether this will or will not commit?

For various reasons, we don't have the ability to add a start transaction before the statement which I assume would automatically rollback if the connection failed before sending a commit.

I suspect the behavior is not defined but thought I'd ask.

Mostly wondering about mysql and pg.


texts: SET NOCOUNT Error in handling SQL call after upgrade

texts:
We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue.

On the new server, our old code will get "operation is not allowed when the object is closed" when executing some stored procedures.  This message never appeared on the old server.  When we tracked it down, the issue can be resolved by adding SET NOCOUNT ON; to the stored procedure.

I looked at the defaults on the database and saw no settings that were different (SQL Server 2008 vs SQL Server 2014) related to defaults.

What setting should I be looking at to resolve this globally without needing to add SET NOCOUNT ON to a thousand stored procs?


texts: How to refresh the URI type data : insert into temptable values (SYS.URIFACTORY.getURI('URL'));

texts:
How to refresh the URI type  data and reload to table , whenever the website url got refresh.

My complete code:

   Created table with URI TYPE

create table vignesh3(url URIType)

   loaded data from the url using geturi function.

INSERT INTO adw.vignesh3 VALUES
  (SYS.URIFACTORY.getURI('http://www.floatrates.com/daily/nzd.xml'));

Loading the columns as a table using xmltable option.

select e.url.getXML() from adw.vignesh3 e

SELECT xt.*
FROM   adw.vignesh3 x,
       XMLTABLE('/channel/item'
         PASSING x.url.getXML()
         COLUMNS
           title     VARCHAR2(100)  PATH 'title',
           link     VARCHAR2(100) PATH 'link',
           DESCRIPTION       varchar2(100)  PATH 'description',
           PUBDATE  varchar2(100)  PATH 'pubDate',
           BASECURRENCY varchar2(60) PATH 'baseCurrency',
           BASENAME  varchar2(60) PATH 'baseName',
           TARGETCURRENCY varchar2(60)  PATH 'targetCurrency',
           TARGETNAME  varchar2(60) PATH 'targetName',
           EXCHANGERATE  varchar2(60)  PATH 'exchangeRate',
           INVERSERATE  varchar2(60)  PATH 'inverseRate',
           INVERSEDESCRIPTION  varchar2(60)  PATH 'inverseDescription'
         ) XT;

I need to refresh this data whenever the url updates the rates.


texts: Move data from one oracle db to another db using toad

texts:
I am new to oracle and this might be a simple question for many.

I need to take data from one db and insert it into other db.  The databases are on the same version and edition.  I am currently using Toad.

All I have been able to find out is to use export and import utilities, which I am not able to execute from my toad client. I'm hoping for a query to retrieve data in a file and then import this file into another database.


texts: What can cause what appears to be missing data in a single table

texts:
I have a situation where it appears as if I've lost data in a single table.

I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the database, for debug and support purposes. Other than the request and response, the logging table also contains an IDENTITY(1,1) column (which is the primary key) and other various columns.

Over a duration of about 15 minutes, it appears as if 2798 rows have "disappeared" from the table. The primary key simply jumps from 3561297 to 3564095.

During the same time period, I've got debug logs from clients of this API which indicates that something went wrong during the request. My IIS log notes that any request which causes database access during this time period had taken between 30000 and 40000 ms to complete.

The odd thing is, that other tables does not appear to have similar gaps. In fact other tables have had data inserted and updated in them, without issue it appears. So it doesn't appear to be a full system crash.

Through my Google-Fu I've come across the fact that gaps in a IDENTITY(1,1) key can be caused by a restart of the server -- and in that case the gap is roughly 1000. However, in this case I'm 100% certain that the database has not been restarted, yet I have this gap.

I've tried reproducing this error by inserting rows and adding a THROW statement in the end, in my test environment to no avail. I've tried to simulate timeouts by both adding a WAITFOR DELAY and making a TABLOCKX, HOLDLOCK on the table, while trying to do a lot of inserts through the application. Every database operation is done through stored procedures btw.

I'm quite frankly stumped. Does anyone have any ideas / hints as to why I've got this apparent data loss in one table? Or what can cause a massive gap in the primary key like this?

As far as I can tell I have no transactions in the logging middlewares SQL. And IMPLICIT_TRANSACTIONS is OFF on the server, and I'm not using XACT_ABORT in the query. How can a transaction rollback occur, if I do not execute a BEGIN TRANSACTION? I thought SQL Server only made "auto transactions" if IMPLICIT_TRANSACTIONS was set to ON?


texts: MS SQL Server Database Option - Transaction Log

texts:
I have a database for staging purpose before move into another main database. It has 5-6 millions records on average. The transaction log keep growing after few days to 30-40 gig. The transaction record not important because I can retrieve from my source again if needed. There are some configuration details needed only.
What's the best way to manage transaction log ?
What database recovery mode should I set - Simple, Full, Bulk Logged ? By default, I selected Full.
Should I just schedule a job to purge the transaction log on a daily basis ?
Any recommendation ? Thanks.


texts: How does sampling work when updating statistics?

texts:
I have several massive tables. I'd like to ensure their statistics are up to date via a weekly maintenance plan.

However, doing so is taking too much time.

If I specify

WITH SAMPLE 50 PERCENT

does SQL Server then sample:

the first 50% of pages
every other page
or some other strategy?

BOL is not clear on this.


texts: In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all?

texts:
A popular answer on SO states that using UUIDs as primary keys has the following benefit:

  Makes replication trivial (as opposed to int's, which makes it REALLY
  hard)

I have been unable to find any evidence in PG's documentation (or anywhere else) to substantiate this claim.  The PG docs mention nothing about this having any bearing on replication.

For me, if a replica is an exact copy of the master, the type of key used is irrelevant.  So, is there something that escapes me?  How would integer primary keys, compared to UUIDs, make harder to replicate your postgres db?

A related question is whether using UUIDs as primary keys slow down join operations (some people claim they do).  Are there any benchmarks on this?  Can anybody share experiences?


texts: Wasted space for compressed SecureFile LOBs?

texts:
When using Oracle BasicFile and SecureFile one property of the implementation is that each LOB occupies multiples of the chunk size (and in case of SecureFile the smallest chunk size is the block size).

This does luckily not happen for data which fits inline in the row, however for that data I could use a normal type. So my concern is more with data stored in the lobsegments - there the overhead can be up to a whole database block (per row) if the LOBs have unlucky sizes.

Does this change when compression is turned on?

I mean if I have a blob which is 7k and compresses to 5k It would still occupy 8k (if this is the DB blocksize). It would be somewhat unfortunate to have a tablespace with extra small blocks for (extra large) LOB segments. (But I understand that SecureFile with dynamic chunk sizes deals much better with this)


texts: Can SHOW TABLE STATUS query performance on MySQL be improved ? - Amazon RDS

texts:
I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57.  There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds.

We have an integration with the UPS Worldship software connecting through ODBC.  It seems that this software running the SHOW TABLE STATUS; query before every function, such that clicking a button requires a 45 second wait.  We had the same database running prior to the new year on dedicated hardware, and we didn't have this problem.

My questions are these:

Is there a way to improve the performance of this query ?
What might be causing such a problem ?


texts: Creating a index on a date column that's already populated

texts:
Is there any downside to creating a index on a already populated date field?

Will the new index perform the same as it would if it was created when the table was?

Would it be costly from a database perspective to add this?

Edit : the table has just under 10 million entries.


texts: How to find out if anything is running as or relies upon a user account in anyway before removing it?

texts:
Running SQL Server 2008.

So I need to get around to removing an old users account, but I'm worried that just disabling or deleting it right away might cause some things to stop working properly if they were setup under that account and/or rely upon it in any way.

Is there a script I can run that will tell me if there is anything that might break if I get rid of a specific user account?


texts: When trying to connect to an external postgres database using pgAdmin, I get an error

texts:
I get the following error when trying to connect:

  "FATAL: no pg_hba.conf entry for host "", user "postgres", database "postgres", SSL off"

The db is installed on an Ubuntu machine in Azure. I have allowed inbound connections on port 5432, so there should be no issue there however.

I have edited and added the following lines to the /etc/postgresql/9.5/main/pg_hba.conf file on the server:

host all all 0.0.0.0/0 md5
hostssl all all 0.0.0.0/0 md5

even when I tried to change the mode to trust, I couldn't get a connection established.

I also edited the listen addresses to be: "listen_addresses = '*'" in /etc/postgresql/9.5/main/postgresql.conf.

I have ran the command service postgresql restart after making each of the changes.


texts: SQL Developer Data Modeller and Data Dictionary question

texts:
I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please?

I can connect SQL Developer to Oracle XE's data dictionary without any problem, i.e. view the information of the data dictionary as an ERD within SQL Developer that shows the tables belonging to HR and the the relationships between these tables expressed as links between the PKs and FKs of the individual tables.

However, when I import the data dictionary from my workplace's 11g Oracle DB into SQL Developer, it brings back all the tables of the schema I'm pointing SQL Developer at but doesn't show the relationships between these tables - i.e. no links between the PKs/FKs of the tables belonging to the schema.

Is this feature something that has to be written into the ERD manually, or should table relationship information be available as part of the tables that make up the data dictionary?


texts:
I'm trying to retrieve logs of our plant where a certain event has occurred.

At the moment, I can query the actual event by using an alarm_code field. However, I'd also like to include about an hour's worth of data before and after the alarm event.

How would I go about doing this?

The timestamp field is also the primary key, if that helps.

Thanks,


texts: Which tables to create according to relations

texts:
We are planning a new system with a new DB (MS SQL Server).

We have the following entities and the following relations:

Entities:
Clubs,
Chains,
PaymentTypes,
etc...

Relations:
Clubs-Chains (M-M);
(Clubs_Chains)-Commission (1-1);
Clubs-Chains-PaymentTypes (M-M);

Commission is a numeric field containing perecentage value , and is not an entity.

We would like the DB to be normalized.
We thought about the following option, but we are not sure if it is the best solution for the scenario:

Clubs_Chains table:
ClubChainID PK int not null,
ClubID FK int not null,
ChainID FK int not null,
Commission int

Clubs_Chains_PayMethod table:
ClubChainID PK int not null,
PaymentTypeID PK int not null

Thanks!


texts: Backup with date to azure blob storage url

texts:
Im trying to do backups to an azure blob storage that have the date when the backup is made, that way the job does not fail when the backup is generated with the same name, I am concatenating convert(varchar(500),GetDate(),112) + '.bak') but im getting the next error in the job execution

Incorrect syntax near '+'. [SQLSTATE 42000] (Error 102).  The step failed.

The script:

Backup database model
to url = 'https:containerurl/azurecontainer/model' + convert(varchar(500),GetDate(),112) + '.bak')
go


texts:
Trying to access remote MongoDB. Got the following error:

C:\Users\ranjj_000>mongo <SERVER_IP_HERE>:28017

MongoDB shell version: 2.6.0
connecting to: 111.93.128.60:28017/test
2014-04-22T12:19:45.084+0530 Socket recv() errno:10054 An existing connection was     forcibly closed by the remote host. 111.93.128.60:28017
2014-04-22T12:19:45.087+0530 SocketException: remote: <SERVER_IP_HERE>:28017 error: 9001 socket exception [RECV_ERROR] server [<SERVER_IP_HERE>:28017]
2014-04-22T12:19:45.088+0530 DBClientCursor::init call() failed
2014-04-22T12:19:45.093+0530 Error: DBClientBase::findN: transport error: 111.93.128.60:28017 ns: admin.$cmd query: { whatsmyuri: 1 } at src/mongo/shell/mongo.js:148

exception: connect failed

Server is: CentOS release 6.4 (Final)


texts: Is binary logs required on the slave in order to do replication?

texts:
I have a MySQL database setup with a master and a slave. As far as I understand, the master writes transaction events to a binary log, which is read and executed by the slave, thus making the slave a continuous copy of the master.

I was under the impression that I needed to make sure that the slave had consumed a specific binary log file before I could safely purge it. But now I have read documents indicating that the binary log isn't really used by the slave in order to facilitate replication. It is instead the relay log which is of interest on the slave. The binary log is only interesting to keep if I need to do point-in-time restores. Is this correct?

If correct, does this mean that I can either turn off binary logs on the slave or purge them all at any time?


texts: Can I pass an SSH Tunnel credentials into a postgresql PgAdmin Connection string?

texts:
I am following this guide for replication across postgresql tables. It is fairly straight forward and so far I have no issues. However, this string is will be a hurdle:
CREATE SUBSCRIPTION subscri_partners CONNECTION ‘dbname = postgres host = 195.83.124.222 user = rep password = Password port = 5433’ PUBLICATION publi_partners;

I have my postgresql db in a docker container which I access through SSH forwarding. I do have PGAdmin successfully connecting to it because PGAdmin has a section to fill in the information to SSH tunnel. However, I can not find any documents on if the connection string supports ssh port forwarding. I see quite a few connections asking this, and answers range from using a Putty SSH tunnel to they plan on implementing this. However, since an ssh tunnel can be put in the config, I would assume it can not be put in this string. Is this true?


texts: Adding an additional permission to my SQL user on every database they already have some permissions

texts:
Our corporate hosting server has had restricted SQL DB permissions for a very long time, but one of our end-user systems has started requiring a new DB permission, INDEX, which none of them have. I can manually grant the INDEX permission to every single database one at the time, but is there a better way to grant every user that has permissions on a database an additional privilege on each database they have permissions for?

Example:

User A has SELECT, UPDATE, DELETE on 35 databases, but now needs INDEX as well. I could go to each database that user A has access to, and manually grant it INDEX permissions, but that is a time-consuming method, and there are hundreds of users and hundreds of databases.

I have tried to run GRANT INDEX ON . to 'user_a', but since the user is not the root user, it doesn't have permissions at all to many databases, nor any of the system databases.  (nor do I want any my users except the root user to have access to any of the system databases)

The problem is compounded in our case because we have well over a hundred users with access to 1 - 100 databases each, that all need to be granted INDEX on all of the databases they currently have some level of permission on, but not on any database they don't already have some permissions on.


texts: How to model a database structure for exam dates and date ranges?

texts:
I have one table Exam, and I want to store exam Date. Each Exam belongs to one of the Categories described below:

Category 1. For Exam A, there can be a Date once in a year, e.g.,  01-01-2017.

Category 2. For Exam B, there can be more than one Date during the same year, e.g., 01-01-2017 and 03-01-2017.

Category 3. For Exam C, there can be a Date range, e.g., from 01-01-2017 to 10-01-2017

Category 4. For Exam D, the Date can be chosen by a Student, e.g., the whole year, i.e., 365 days

My approach

I am saving the Exam.Date(s) in another table. I believe the relationship between Exam and Date is one-to-many. Said table would display data as follows:

Question

The problem with this approach is that, for Category 4, each Exam will have 365 rows, so, is this approach correct?


texts: How to Drop Tables using a variable in SQL Server?

texts:
DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.

WHY?:
In my Access Database I am running a transaction where a bunch of new SQL tables will be created on the fly... but if for some reason one of these tables fails to be created then I need to completely roll back and Drop any of tables that were created in the Back-End.

QUESTION:
Is it possible to create a generic stored procedure to drop any SQL table depending on the name I pass through.

NOTE: I have only just started learning SQL code thus I have minimal understanding of SQL code and syntax so describing in layman's terms would be much appreciated.

SO FAR:

    CREATE PROCEDURE dbo.procdroptable
        @TABLENAME SYSNAME
    AS
    BEGIN
        SET NOCOUNT ON;
    DECLARE @ SQL NVARCHAR(MAX)
    SELECT @ SQL 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC Sp_Executesql @SQL;
    END
    GO


texts: Can additional indices ever aggravate lock contention?

texts:
Not considering index creation: Can it ever be disadvantageous to create an index on a table with regards to lock acquisition?

I have experienced already some cases where a carefully selected index has enabled much higher parallelism in a database, as is expected by (and documented for) MySQL's use of index (gap) locking.

But is the opposite possible as well, i.e. two or more queries from different transactions that will not get in each other's way without some index, but which given an additional index will incur additional waiting on a lock?

My question is not limited to a single RDS implementation, but I am most interested about MySQL and Postgres.


texts: What are the pros and cons of using the Sphinx Storage Engine?

texts: What are the pros/cons of using the Sphinx Storage Engine in MySQL (doc) over the normal api?


texts: Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS

texts:
Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:

Exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER/SCHEMA', tabname => 'MYTABLE', cascade=> true, degree=> 8);

The "CASCADE" attribute was defined as: Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.

So my question is, should I run the REBUILD of my indexes prior to GATHER_TABLE_STATS, as I'm assuming gathering the index stats and rebuilding them IS NOT the same thing, or is it?

At the moment, I run dbms_index_utl.build_table_indexes to rebuild my indexes followed by the GATHER_TABLE_STATS,  I just don't know if it's necessary, mind you my explain plan seems to indicate it is, otherwise, unless I add a hint to my query, the index won't get used.


texts: Query for lowest price for product in same row

texts:
I have two tables: first for products and second for prices.

I need to calculate and display the minimum price from all 5 prices for one product.
I already have query to select five prices and dont know what to do next:

SELECT
    wp_prices.price_one,
    wp_prices.price_two,
    wp_prices.price_three,
    wp_prices.price_four,
    wp_prices.price_five
FROM wp_products
JOIN wp_prices ON wp_products.id = wp_prices.product_id
WHERE wp_prices.product_id = ' . $current_product_id . '


texts: SQL Server - Select last value given a date

texts:
To make it easier, I have data that looks like this as an example:

What I am trying to return is the Amount of the Balance Update as of a specified Date for each Investor_ID.  For example, if my date is 12/31/15, It would return two rows:

My query is:

SELECT Investor_ID,
(SELECT Max(CASE WHEN Type = 'Balance Update'
                 THEN Amount else 0 END)) as Val
FROM Table1
WHERE Date <= '12/31/2015'

However this returns the maximum value of "Amount" for the date range (before 12/31/15) as opposed to the Amount at the Maximum date, 12/31/15.

How can I restructure the query to where the Date can be changed, and return the LATEST "Amount" of "Balance Update" for each Investor_ID?

Thank you!


texts: Having multiple refs in an intersection table

texts:
I have two tables, BOOK and JOURNAL.  I want to store their authors in an intersection table called AUTHORS.  Is it acceptable practice to have mulitple reference id columns (kind of FK's I think) to other tables i.e. BOOK and JOURNAL?  This is to save having separate author tables for Books and Journals.
So, AUTHORS would look like:

ID
BOOK_ID
JOURNAL_ID
NAME

0
54
212
A.R Smith

1
598
NULL
J.R. Hartley


texts:
I'm trying to perform a restore of a database from a different server onto my localhost server .That database is encrypted (the bak file ) ,so I have to decrypt it ,that's fine I have all the cert files and the password ,but for some reason I'm still getting the following error :

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

This is the code I'm executing:

      CREATE CERTIFICATE [Certificate1]
FROM FILE = 'C:\Location of the certs'
WITH PRIVATE KEY (
    FILE = 'C:\Location of the certs' ,
    DECRYPTION BY PASSWORD = 'password'
);

PS. I'm no expert at backups and restores .


texts: Select rows from table having specific values depending on another values in the same table

texts:
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore  7 and 8 example:

Userpin  TrDate       Time1      Tr_type
4555     12-02-2020    7:30         7
4555     12-02-2020    14:30        8
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7
8542     12-02-2020    7:16         7
8542     12-02-2020    14:40        8

Using this data set I am looking for Query To Get
that data with userpin that have only tr_type equal 7 in current day
 and i want the result like that  :

Userpin  TrDate       Time1      Tr_type
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7

i used that query to get data in current day

select UserPIN ,TrDate,Time1,Tr_Type
from mytable
where UserPIN in (6985,9854,4555,8542,4062,7070,4189,4197)
and TrDate=CAST(GETDATE() AS DATE)

This seems like a simple thing to query, but I am finding myself up against a wall. What's the best way to accomplish this in a reasonably efficient way?


texts: How to create an Event Notification that runs a Job/procedure when mirroring state changes

texts:
I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?

Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to understand and make everything he says.

So far I think what I need to create a notification event for DATABASE_MIRRORING_STATE_CHANGE, am I correct?

how can I create this event notification to when its triggered inserts a line in a table, that stores a timestamp and an ID that comes from the notification.

so far I'm setting up one alert per ID, each one running a job like this(this example is  for ID = 1):

    DECLARE @state AS varchar(50);
    SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
    IF (@state IS null) SET @state = ' ';
    INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), 1, 'Principal synchronized with W ', @state, @@SERVERNAME)

Basically I'm creating an internal log in this database:

CREATE TABLE [dbo].[MirroringAlerts](
    [DateTime] [datetime] NOT NULL,
    [alertID] [smallint] NOT NULL,
    [alertDesc] [nchar](50) NOT NULL,
    [Sync] [nchar](12) NOT NULL,
    [alertCreator] [nchar](128) NULL
) ON [PRIMARY]

But this way...the alerts are not being triggered fast enough...so I'm loosing information...

Can you tell me how to program this behaviour with create event notification for Database Mirroring State Changed event?

Best Regards


texts: Identify unattached File Size in SQL Server

texts:
From a similar post on this forum, I have modified the below code as per comments and able to get the orphaned files in the drives. The code is :

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

if object_id('tempdb.dbo.#os_files') is not null
    drop table #os_files
create table #os_files([filename] varchar(2000))

insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.mdf /b /s'
insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where
    db.database_id is null

EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

Is it possible in any way to modify the above code and get the size of the files in the output apart from physical file names?
Also is there any other way to modify the above code to search for files other than .mdf and .ldf files in the content volumes, where the database and log files reside. In our environment we have folders where mdf and ldf files are kept separately. Hence, the need.

The output that I'm expecting should be like this:

PHYSICAL_FILENAME      SIZE
        file1             s1
        file2             s2

Kindly look into this issue and give your suggestion.
Thanks in advance.


texts: Computing values on the fly vs decrementing bucket contents

texts:
Imagine you have an employee database and a table for each employee which contains their start date.  There are other tables which describe their vacation allowances based on role, seniority and month of hire.  There is also a table which contains the vacation used for each employee.

The question:

From a general, balanced perspective of performance and ease of code and maintenance, is it better to compute an employee's vacation balance by evaluating all the table data or to store the available vacation in buckets which are then adjusted as vacation is taken.

It seems to me the overhead and complexity of calculating the available vacation, then computing the vacation which has been taken is unnecessary and a more efficient and graceful solution would be to create a table which holds the available vacation, populate it at the beginning of the year and decrement it as vacation is taken.


texts:
I have RFID readers currently writing data to files on a hard drive, which then SSIS reads and inserts into the database. Instead, I'd prefer that SQL Server (2016+) read the data stream directly into a Memory Optimized Table to increase performance and handle scalability.

What is the best method to do this, handling multiple readers sending data all at once, and making sure no data gets lost due to  bottlenecks, deadlocks, etc.?

Each reader can handle approximately 60 reads per second, then transmit via wi-fi (or later via USB cradle).  So per minute, we're talking 60x60x512bits of data per reader.  For future, assume in large warehouses that there could be 10+ readers going at once.

There is simultaneous processing and reading of the data.  While the reader data is coming in, it needs to be checked for inconsistencies, redundancies, de-duped, updated (e.g. if a product location has moved), etc.  Then, queries / reports will be made against the DB - various user searches, inventory and location reports, analysis.

Also handling of bottlenecks due to simultaneous delivery of data by readers - i.e. say 5 readers were delivering their daily data at the same time through cradles, while the DB was doing various other tasks.


texts:
I have a query with following explain plan

EXPLAIN SELECT COUNT(applicant.id) as applicantCount,
    application.name,applicant.application_id,application.id
FROM  applicant
  LEFT JOIN application
    ON applicant.application_id = application.id
  LEFT JOIN calendar
    ON applicant.application_calendar_id = calendar.id
  LEFT JOIN application_category
    ON application_category.id = calendar.category_id
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'
GROUP BY application.name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: application_category
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: calendar
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: applicant
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2333634
        Extra: Using where; Using join buffer
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: application
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: eRecuitment_TestQueries.applicant.application_id
         rows: 1
        Extra:
4 rows in set (0.00 sec)

Query takes 30 seconds to complete where tables application, application_category and calendar are master tables which have 3 to 4 records only. The applicant table has about 2400000 records.

Is there any other approach to rewrite/optimize this query?

Index details

Table application

PRIMARY KEY (`id`)

Table applicant

PRIMARY KEY (`id`),
KEY `idx_abdul_test` (`application_id`)

Table Calendar

PRIMARY KEY (`id`)

Table application_category

PRIMARY KEY (`id`)


texts: Adding a column to aggregated data where new value only applies to a subset of the aggregation

texts:
Having trouble explaining this.

I have a table like this:

Label | Value
A     |   3
B     |   1
C     |   4

And another like this:

Label | Value | ID
A     | 1     | ID1
A     | 1     | ID2
C     | 4     | ID3

I need to add the ID column to the first table, splitting some rows according to the Value in the second table. Resulting in this:

Label | Value | ID
A     | 1     | ID1
A     | 1     | ID2
A     | 1     | NULL
B     | 1     | NULL
C     | 4     | ID3

If the Label and Value columns were identical for both tables, I could easily do join table2 ON table1.Label=table2.Label and table1.Value=table2.Value but the unaggregation aspect of this is throwing me off.

Apologies if this is worded poorly.


texts: SQL 2012 Availability Group issue - The core cluster resources do not move when we initiate AG fail-over

texts:
I have a Windows 2012R2 cluster with two nodes and a File Share quorum. I am running SQL 2012 SP2 and I have configured a test AG with a dummy database.

When I fail-over the AG, the Availability Group Listener and the associated Availability Group Role fail-over as expected, but the Cluster Core Resources, specifically the Cluster Virtual Network Name, does not. In order to move the Cluster VNN I need to right-click on the cluster, go to more actions and select "Move Core Cluster Resources", but shouldn't this be occurring automatically?

I think I need to specify a dependency somewhere, but for the life of me I cannot find where.


texts: How to search with partitions in my query?

texts:
Table structure:

mysql> show create table transaction_event_items\G
*************************** 1. row ***************************
Table: transaction_event_items
Create Table: CREATE TABLE `transaction_event_items` ( `tei_row_id` bigint(20) NOT NULL AUTO_INCREMENT,`tei_adp_type`
varchar(16) NOT NULL,`tei_trn_code` varchar(64) DEFAULT NULL,`tei_trn_date` datetime NOT NULL DEFAULT '2020-01-01 01:00:00',
`tei_item_seq` int(11) DEFAULT NULL,`tei_item_cln` int(11) DEFAULT NULL,`tei_item_type` varchar(16) NOT NULL `tei_item_code`
varchar(32) NOT NULL,`tei_item_date` timestamp NOT NULL DEFAULT '2020-01-01 01:00:00',`tei_item_amount` decimal(8,2) DEFAULT NULL,
`tei_item_description` varchar(128) DEFAULT NULL,`tei_att_row_id` int(11) NOT NULL, `tei_lastupdt_date` timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP,`tei_phy_id` varchar(64) DEFAULT NULL,`tei_item_quantity` int(10) DEFAULT '1',`tei_item_employee` varchar(64)
DEFAULT NULL,KEY `tei_row_id` (`tei_row_id`),KEY `tei_phy_id_trn_date_idx` (`tei_phy_id`,`tei_trn_date`),KEY `tei_item_code_date`
(`tei_phy_id`,`tei_item_code`,`tei_trn_date`),KEY `tei_item_description_date` (`tei_phy_id`,`tei_item_description`,`tei_trn_date`),
KEY `idx_tei_item_date` (`tei_item_date`) ) ENGINE=MyISAM AUTO_INCREMENT=2845272905 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE
(to_days(`tei_trn_date`))(PARTITION p111109 VALUES LESS THAN (734816) ENGINE = MyISAM, PARTITION p111110 VALUES LESS THAN (734817) ENGINE = MyISAM,
PARTITION p111111 VALUES LESS THAN (734818) ENGINE = MyISAM,
PARTITION p111112 VALUES LESS THAN (734819) ENGINE = MyISAM,
...
PARTITION p121129 VALUES LESS THAN (735202) ENGINE = MyISAM,
PARTITION p121130 VALUES LESS THAN (735203) ENGINE = MyISAM,
PARTITION p121201 VALUES LESS THAN (735204) ENGINE = MyISAM,
PARTITION p121202 VALUES LESS THAN (735205) ENGINE = MyISAM)

Query:

SELECT
    tei_phy_id as Physical_ID__c
  , max(tei_trn_date) as Last_Event_Received__c
  , tei_adp_type as Last_Event_Adapter_Type__c
FROM
    transaction_event_items t
WHERE
    tei_trn_date > date_sub(now(), interval 2 day)
GROUP BY
    tei_phy_id;

Explain:

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 10072085 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+

This query is not using any indexes and partitions in the table. Table size is 160gb.

What can I do to make it use indexes and partitions?


texts: Job Information - When querying [msdb].[dbo].SYSJOBS_VIEW through linked server

texts:
I am trying to build a report to query all my sql server jobs and thier status from the previous night's run.  In an effort to have just one report instead of a separate report for each server, I am using linked server connections to query each sql server from the report.

When I try to run the query through a linked server connection, I don't get the data for the column originating_server, in the [msdb].[dbo].SYSJOBS_VIEW, or any columns in this view.  This is so I know where the job is being run.

When I run the query on a local server, it runs fine and gives me the originating_server as expected.

Is this a permissions issue?  The user connecting has db_datareader on the msdb database on the linked server.

DECLARE @YEAR INT,@MONTH INT,@DAY INT,@DAY2 INT
DECLARE @DATEs VARCHAR(10),@DATES2 VARCHAR(10)
DECLARE @LENTGH VARCHAR(8)
SET @YEAR=YEAR(GETDATE())
SET @MONTH=MONTH(GETDATE())
SET @DAY = DAY(GETDATE())
SET @DAY2 = DAY(DATEADD(DAY,-1,GETDATE()))
SET @DATES =CONVERT(VARCHAR(10),GETDATE(),121)
SET @DATES2 =CONVERT(VARCHAR(10),DATEADD(DAY,-1,GETDATE()),121)

SELECT JOB.NAME
,JOBVIEW.ORIGINATING_SERVER AS SERVERNAME
,CASE
    WHEN JOB.ENABLED=1 THEN 'ACTIVE'
    WHEN JOB.ENABLED=0 THEN 'INACTIVE'
    END ACTIVE
,CASE
    WHEN LAST_RUN_OUTCOME=0 THEN 'FAIL'
    WHEN LAST_RUN_OUTCOME=1 THEN 'SUCCEED'
    WHEN LAST_RUN_OUTCOME=3 THEN 'CANCEL'
    ELSE 'UNKNOWN'
END STATUS
,LAST_OUTCOME_MESSAGE
,LAST_RUN_DATE
,NEXT_RUN_DATE
,LAST_RUN_DURATION
FROM [linkedserver].[msdb].[dbo].SYSJOBS JOB
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSERVERS JOBSERVER ON JOB.JOB_ID=JOBSERVER.JOB_ID
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBSCHEDULES JOBSCH ON JOB.JOB_ID = JOBSCH.JOB_ID
LEFT OUTER JOIN [linkedserver].[msdb].[dbo].SYSJOBS_VIEW JOBVIEW ON JOB.JOB_ID = JOBVIEW.JOB_ID
WHERE LAST_RUN_DATE IN (REPLACE(@DATES,'-',''),REPLACE(@DATES2,'-',''))

When I give the user sysadmin, I get the information I need, but I don't really want this user to have sysadmin. Is there a specific permission I can grant to the users?


texts: SQL Server backup very large compared to table size

texts:
I have a database with 31 tables and my largest table has about 12 columns and 750,000 records. I checked the size of all my tables using this query:
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name

and it says that my largest table has 147.45 MB of data:

When I total all of the space used from the tables it is not even 200 MB. However, when I create a backup file it is 4.5 GB.
I am not sure what is going on.


texts:
I have this table

CREATE TABLE `LastPosition` (
`Id` CHAR(36) COLLATE utf8_bin NOT NULL DEFAULT '',
`Serial` BIGINT(20) NOT NULL,
`DateGps` DATETIME NOT NULL,
PRIMARY KEY USING BTREE (`Id`, `DateGps`),
KEY `serial_idx` USING BTREE (`Serial`),
KEY `dategps_idx` USING BTREE (`DateGps`),
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

And this query

SELECT
    Serial,
    MAX(DateGps) AS maxi
FROM
    LastPosition
GROUP BY
    Serial
ORDER BY
    DateGps

How can I get the id of the record with the max of DateGPS?


texts: Cannot create a database connection SQL Developer

texts:
I will try to explain the current situation in the best possible way.

Well, I got a VM with SQL Developer already installed but could not run properly. After some reasearch, I solve the problem related with environment variables.

Currently, I need to create a database connection but unsuccessfully. I do not know what are the credentials that I have to use it. I can log in on sqlplus as sysdba.

I already did the select * from all_users but I am still stuck on this. Is there anyway to discover the right credentials to create the connection?

Thanks anyway.


texts: Upon a successful connection to the MySQL server, the client is greeted with "Welcome to the MySQL monitor". A web search returned many results on monitoring for MySQL databases. Is "monitor" here another name for "server"?


texts: Postgresql - newer identical index is used, older one used to be but is not anymore

texts:
Postgres version 11.2

I've got a table with about 30 million rows and a particular index that up until recently was actively being used.

And then it stopped being used and the query that the index was made for, is now doing sequential scans.

I discovered this when I was investigating the query and forgot to check which indexes the table already had, so I made a new one. Which turned out to be exactly identical in construction to an existing one.

explain showed that the new one was being used just fine. Removing it again, vacuum analyze-ing the table and doing the query again, showed the old index not being used. A sequential scan was opted for instead.

Deleting and re-creating the index worked. Now it suddenly was being used again.

It stayed like this for a few weeks and now I'm at this scenario again.

What's going on here?

Googling for this stuff just gives the old "why postgres may decide not to use your index" which isn't exactly the scenario at hand, I think.

Also, I used recent module "amcheck" https://www.postgresql.org/docs/11/amcheck.html

It comes back fine for this index.

EDIT:

Size of the index not being used is 101MB.
Size of the index being used is 16MB.

Query used for determining this: https://wiki.postgresql.org/wiki/Index_Maintenance#Index_size.2Fusage_statistics

While both small, it seems like a significant difference and I wonder where it came from.

There's other indexes on that table which are over 10GB, that show as being used...

Wasn't sure how to find that, just did select * from pg_index where indisvalid = false; and that returned an empty list.
Here is the output for index used: https://explain.depesz.com/s/H5X9y
Here is the output for index not used: https://explain.depesz.com/s/n6bP


texts: What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)?

texts:
I have a corrupt database.  The last known good backup was over 2 weeks ago.  I've run a CHECKDB and have the page numbers where SQL Server located the corruption.  I've looked at the data on the pages before and after the corrupt pages using DBCC PAGE and I am certain that the corrupt data is contained to a single day back in 2013.  The last known good backup, only being 2 weeks old, will have that day from 2013 on clean pages.

My plan is this:

Restore last known good backup to a new database.
Run DBCC CHECKDB (corrupt_database, REPAIR_ALLOW_DATA_LOSS) on the corrupt database and let it correct the issue.
Insert the deleted records back into the old database from the new database.

I'm afraid of running REPAIR_ALLOW_DATA_LOSS because everything I've read online says it should be used as a last resort.  I'm not sure exactly what it will affect.  If it deletes pages, will it only touch the pages that CHECKDB found as corrupt?

But...If REPAIR_ALLOW_DATA_LOSS removes those corrupt pages, but I know what data I will lose, can I not just restore the backup to a new database and insert the data that was deleted in the old database?  I hope I'm making sense.

Please let me know your thoughts.


texts: SSIS - Disable Task Based on Variable Value

texts:
I have an SSIS package that runs a number of Data Flow Tasks all at the same time.

Each Data Flow Task is placed inside a Sequence Container.

Before the Data Flow Tasks are run, a Script Task is used to check what Data Flow Tasks are required to run.

Each Data Flow Task also has a related variable, which is set to true or false by the Script Task. The Data Flow Tasks use the variable value as a Disable Expression property.

When run initially, the process works as expected. Those Tasks that have variable value = true do run, and those that have a variable value = false do not run.

However, the next time I go to run the package all Data Flow Tasks are disabled initially (probably because the related variables are set to false).

But even when some variables are then set to true at runtime, the Tasks remain disabled. The Disable Expression does not seem to pick up the latest variable value. (in the Watch window I can see the variable values have updated correctly)

I tried setting 'DelayValidation' = true on each Task but no change.

Any reason why this happens?


texts: Deadlock occurring on database, SQL Server 2008R2

texts:
Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ALLOW_SNAPSHOT_ISOLATION ON

I believe my log file will grow faster than before now?

Are there different types of isolation and is it possible to set the isolation to just 2 tables which causes the deadlock?

::Update::

::2013-03-21 15:09::

Can I query the process ID's involved in the deadlock? select object_name() returns null always


texts:
I have a quick question, if someone can help, I would be grateful

I have the following tables
User, Video, User_video

  user has userdetails (user_id, name, email)
  video has videoDetails (video_id, video_url, video_price)
  user_video is the mapping table which has (user_id,video_id,price,dttm)

currently, if I do a select from video table, it gives me all available videos which is fine. The current return format is as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00

So far, so good, but now, I need to get the details of the logged in user, as in which videos he has purchased and which ones he has not, so in the above response, next to each video, I need a "Y" or "N" flag (by referencing the user_video) table.

I need the output as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00 | Y
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00 | N

It is a PostgreSQL database. And I am not an expert on databases, I am familiar with only the basics.

If someone can guide me in this regards, it will be most helpful.


texts:
I have one master and four slaves. Sometimes all my slaves lag behind the master. I have implemented the heartbeat for monitoring replication lag. Now I am trying to find why the slaves are lagging behind the master.

I saw the slow queries (for today) on the master and I found that the slowest query (DML) was taking 138 seconds. But the slaves were lagging about 1400 seconds and there were also no slow queries on the slaves for DML (update, delete, insert, etc.).

Points to be taken into consideration:

All tables are InnoDB.
68 GB of RAM (Master as well as slaves).
Data size about 1 TB.
Master and slave are running from a long.

What may be the reason for lag?


texts:
I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft Access 2010 to work with, but I do require to move this data back and forth between Access and Excel. Since then, I have made a lot of changes to the data and I now want to export this data back from Access into Excel, with the data appearing in the same columns that they were imported in from. I have tried exporting the data back into Excel, but it doesn't look the same as the excel file that I originally exported the data from.

I was wondering if there is a way to export all of the data in Access back into Excel so that it is formatted exactly the same way it was before it was exported into Access

Sorry if this is a noobish question, I'm new to Access and don't have much experience with it yet.

Thanks


texts: Is there any point with a combined index on all columns in a table?

texts: I have stumbled upon several cases of indices set on all columns of a table. In my mind this will be as efficient as the full table scan that would occur without the index. Is this correct or is there any benefit of such an index? I have seen this in Active Record on top of Postgres so this setup is my primary concern. I also wonder if and how this may differ between the different databases.


texts: Dynamo get items from last X days using composite key

texts:
I have the following sort key in a dynamo db table:

YYYY-MM-DD#<USER_ID>#<CATEGORY_ID>

Im trying to solve the following two access patterns:

get all item for a user in the last x days
get all items for a user and category in the last x days

I can satisfy the 2nd access pattern with the following conditional expression:

sort_key BETWEEN 2018-01-02#<USER_ID>#<CATEGORY_ID> and 2019-01-02#<USER_ID>#<CATEGORY_ID>

This returns several items between 2018-01-02 and 2019-01-02

However, if I try to satisfy the 1st access pattern I cant get it to work. I've tried:

sort_key BETWEEN 2018-01-02#<USER_ID> and 2019-01-02#<USER_ID>

But only one item is returned, the item with date 2018-01-02, but I should be getting multiple items returned.

I searched online for any wildcard operators with conditional expression, but couldn't find any. Any ideas how I can satisfy access pattern 1?


texts: Postgres requested WAL segment has already been removed (however it is actually in the slave's directory)

texts:
I am using repmgr as my replication tool. On the slave I keep getting an error:

  requested WAL segment  has already been removed

When I check the Master indeed it is not there; however, it is in the slave's directories both in pg_xlogs and pg_xlogs/archive_status. I can't understand why it would be looking for this file if it's already in the slave?

In fact it has xlogs going past the requested one. The solutions in What to do with WAL files for Postgres Slave reset are for a slightly different problem. They seem to be for a scenario where the master deletes a log file before the slave receives it. In my case it is very much present on the slave and several other files in the sequence after the one being requested.

This also tells me I do not need to increase the keep wal segments option as it didn't seem to fall behind?


texts: How to give user access permission to dbo and single custom schema?

texts:
So I created the following users which owns single schema:

userA -> owns -> schemaA
userB -> owns -> schemaB
userC -> owns -> schemaC

The userA should be able to access dbo & schemaA schemas, but should not be able to access (i.e select/update/insert/delete...) schemaB data!

May be I can do something as below:

DENY SELECT ON schema::[schemaB] TO [userA]
DENY DELETE ON schema::[schemaB] TO [userA]
DENY UPDATE ON schema::[schemaB] TO [userA]
DENY INSERT ON schema::[schemaB] TO [userA]

GRANT SELECT ON schema::[dbo] TO [userA]
GRANT DELETE ON schema::[dbo] TO [userA]
GRANT UPDATE ON schema::[dbo] TO [userA]
GRANT INSERT ON schema::[dbo] TO [userA]

However, is there any better approach for doing this, so the userA can only access dbo and schemaA, and not schemaB & schemaC? And same vice-versa for userB & userC!


texts: Is MSDTC required for SQL Server 2012 Fail Over Cluster?

texts:
I am making a 2 node SQL Server 2012 Failover Cluster; do I need to install the MSDTC component?

If yes, can both be installed on single shared disk?


texts: Why not have Persons for every person in schema?

texts:
I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect.

Why not have a schema where every individual is a person and is in Persons table and if went to look for "student", we call "students" table which links its person id to the id in Persons table to get data.

Students may become teachers so the data from students table can be updated/removed and be added in "teachers" table.

Why is that not common? What would be a problem with having "Persons" table rather than to have a table of people of a given position?


texts: Automating an SQL task on a monthly basis

texts:
At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and restore it from the share.

When January is completed at the end of the month we repeat the process for the Feb - this time we take a copy of what has been worked on during the month of Jan by the team and back that up into an archive. The February 1st database then goes live onto the dev server.

Is it possible to automate this process? Our techie left us recently and didn't document this process so I've managed to do it manually thus far but imagine it can be automated.


texts: MySQL NDB Cluster settings to prefer data node on localhost than remote

texts:
My current MySQL NDB Cluster configuration:

Server 1 - management node
Server 2 - huge machine running both ndbd and mysqld
Server 3 - huge machine running both ndbd and mysqld
NoOfReplicas = 1

So server 2 has exact copy of server 3 data

During setup evaluation I discovered that mysqld on server 2 is sending about 50% queries to ndbd running on server 3.

With greater number of nodes this would be OK to provide load-balancing, but with my specific configuration (both nodes have same dataset) I'd like to get max performance by telling mysqld on each server to PREFER connecting to ndbd process running on localhost, and only connect ndbd on remote host only if local is not available (ie restart)

I've read something about server access cost table that should contain information for query optimizer about latency between nodes, but I can't find this blog post right now.

Any help?


texts: How to set Auto_Increment value based on a variable?

texts:
Is it possible to set the auto-increment value in MySQL based on a variable?

SELECT 1 + MAX(id)
INTO @newAiValue
FROM Result_Import_Archive;

ALTER TABLE Result_Import
AUTO_INCREMENT = @newAiValue;

This is what I intend to do, however, MySQL complains that it is syntactically incorrect.


texts: What is the Best Way to detect if server is a Master inside a Stored Procedure?

texts:
I'm want to create a stored procedure/event to do some maintenance tasks.  My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be promoted in the event of primary master failure).

I'd like to have this stored procedure run in the event scheduler on both.  During the normal course of operation I'd like it to do it's thing and let those operations flow through the normal chain of replication.  In the event of failover I'd like this to just detect it's not the master and continue the work on what was previously the slave.  (Less things to worry about getting in place during such an event).

I was thinking wrapping the initial call in a block of pseudo code that looked like

if I_AM_MASTER then

call real_work_sp;

end if;

What's the best way to test for I_AM_MASTER?  I know I can do show master status from the commandline, but I'm not sure how to capture that for purposes of this test.  How would I do that, or is there a better way?

P.S. The operations aren't just deletes I could just let "fall through" if they happened to get run twice.


texts: Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?

texts: Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when creating user databases.


texts:
I'm using mongodb 3.6.18
I'm running a cluster of 2 shards and 1 config replicaset.
I get this error when trying to set the compatibility version to 3.6:
global:PRIMARY> db.adminCommand( { setFeatureCompatibilityVersion: "3.6" } )
{
    "ok" : 0,
    "errmsg" : "expected entry { _id: \"son.service_state\", lastmodEpoch: ObjectId('5f743676f790314e2a02cfd3'), lastmod: new Date(4294967296), dropped: false, key: { data_center: 1 }, unique: false } in config.collections for son.service_state to have a UUID, but it did not",
    "code" : 1,
    "codeName" : "InternalError",
    "$gleStats" : {
        "lastOpTime" : {
            "ts" : Timestamp(1601465931, 1),
            "t" : NumberLong(1)
        },
        "electionId" : ObjectId("7fffffff0000000000000001")
    },
    "$configServerState" : {
        "opTime" : {
            "ts" : Timestamp(1601465922, 1),
            "t" : NumberLong(1)
        }
    }
}

Did anyone encounter this before? how can i fix this?


texts: Could not obtain exclusive lock on database 'model' - who is using it?

texts:
when trying to run the following query in order to create a new database:

    CREATE DATABASE [Lunch]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'Lunch',
FILENAME = N'E:\Data Files\Lunch.mdf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB ),
     FILEGROUP [DATA]
    ( NAME = N'Lunch_Data',
FILENAME = N'E:\Data Files\Lunch_Data.ndf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB ),
     FILEGROUP [NONCLUSTERED_INDEXES]
    ( NAME = N'Lunch_nonclusteredindexes',
FILENAME = N'E:\Data Files\Lunch_nonclusteredindexes.ndf' ,
SIZE = 110592KB , FILEGROWTH = 1048576KB )
     LOG ON
    ( NAME = N'Lunch_log',
FILENAME = N'F:\logFiles\Lunch_log.ldf' ,
SIZE = 524288KB ,
FILEGROWTH = 524288KB )
    GO

Getting the following error message:

  Msg 1807, Level 16, State 3, Line 1

  Could not obtain exclusive lock on database 'model'.

  Retry the operation later.

  Msg 1802, Level 16, State 4, Line 1

  CREATE DATABASE failed.

  Some file names listed could not be created.  Check related errors.

what is using the model database and does not allow me to obtain the exclusive lock?


texts: Nothing is written to log in log_directory() for a PostgreSQL cluster

texts:
Running PostgresSQL 11.4 on Windows 10

I made a DB cluster in  C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1

I registered that cluster to start on as a windows service on startup:

pg_ctl register -D C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1 -N PostgreSQLdb1 -U WindowsUser -P WindowsPassword  -S a

And the Where to log In C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1\postgresql.conf I changed the log parameter to a absolute location.:

# - Where to Log -
log_destination = 'stderr'      # Valid values are combinations of
          # stderr, csvlog, syslog, and eventlog,
          # depending on platform.  csvlog
          # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on      # Enable capturing of stderr and csvlog
          # into log files. Required to be on for
          # csvlogs.
          # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1\log'
                      # directory where log files are written,
          # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
          # can include strftime() escapes

However, the cluster database does not write anything to log. The main PG database is located in a totally different directory, and that database does write to its log file.

Both databases works just fine, except the log part.

I have have even restarted my PC, but it still does not write to log.


texts: With MS SQL Server are the generated constraint names predictable?

texts:
When you create your primary key, and foreign key constraints in a CREATE TABLE script, there are named constraints that are created, such as FK__RecentlyVi__ScId__2764765D ... are these constraints predictable?  IE: if you run the same creation script on another server, will the constraint name be the same?

I ask because with entity framework, when you have multiple references to a secondary table, you get properties like...  Foreign, Foreign1, Foreign2, etc... and sometimes when re-generating the entity model the order is different...  I've come up with the following to work around this, but want to know if the "default" constraint names will work, even though I'm now using named constraints.

My workaround is included below.  If I need this again in the future, may refactor out getting the property of an entity based on the foreign key, and object type name.

private Contact GetContactMatchForForeignKey(string foreignKeyName)
{
  var props = typeof(Order).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) {
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") {
        return (Contact)prop.GetValue(this, null);
      }
    }
  }
  return null;
}

private void SetContactMatchForForeignKey(string foreignKeyName, Contact value)
{
  var props = typeof(Contact).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) {
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") {
        prop.SetValue(this, value, null);
        return;
      }
    }
  }
}

public Contact Purchaser
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}

public Contact Seller
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}


texts:
I have a number of SSIS Packages

I am trying to locate a table that is populated by one of the SSIS Package

is there an easy way to find in the project where database.dbo.tabledata has been used in a script within the project

please help team


texts: How to perform a 3 table join with sparse lookup table

texts:
I have 3 tables companies, products, and companies_products.

companies (id, name)
products (id, name)
companies_products(company_id, product_id)

companies_products is sparse, in that the only entries in it are for products that a company has subscribed to - there is no entry for non-subscribed products). I am doing this to prevent having to update every company in the event that a new product is to be added.

Unfortunately this makes getting list of what a company is and isn't subscribed to rather difficult for me to figure out

Is there a way to query for a result from this data:

company

(id: 1, name: "Test Company")

products

(id: 1, name: "Product 1")
(id: 2, name: "Product 2")
(id: 3, name: "Product 3")

company_products

(company_id: 1, product_id: 1)
(company_id: 1, product_id: 2)

to get a result such as this?

(company_id: 1, product_id: 1, status: "enabled")
(company_id: 1, product_id: 2, status: "enabled")
(company_id: 1, product_id: 3, status: "disabled")


texts: What are the risks (if any) of sharing a service account on more than one server?

texts:
I use domain accounts for SQL Server service accounts.  Sometimes, when I have multiple servers that are logically or thematically related, I'll use the same set of domain accounts for the service accounts on all of them.  The user account permissions on each server may be different, but there's usually a lot of overlap.

The gist of my question is this: can a user with access to one instance, but not the other, exploit the shared service accounts to gain access to the other server?

Here's the specific situation I'm trying to address:

I have two servers with default SQL instances, call them org-sql-1 and org-sql-2.
I use a domain account for each service, so I'd have mydomain\orgDBService, mydomain\orgAgentService, etc. (although I'm not sure that's strictly relevant -- I think I'd have the same question if I used a single account for all services).
But I reuse these domain accounts on both servers, so the database service on both org-sql-1 and org-sql-2 runs under mydomain\orgDBService, for example.

To date, I've never particularly questioned the security implications, since these servers have a common purpose and user base, even if the specific user permissions may differ.

Now, we're about to add a third instance, let's call it org-sql-rpt.  This one is a good thematic fit with the others, but there's one key difference: we're going to allow an external partner (i.e. not an employee of our org) access to this server, like so:

Their credentials will own (belong to db_owner) a couple databases.
But they won't belong to any Server Roles (other than public, of course).
They may have admin access to the OS desktop for a limited time, prior to making this server live.
They won't know the passwords for the service accounts.

Given that, should I worry about reusing the domain accounts as service accounts for this new instance?  Is there any risk that this person could use their legitimate credentials on org-sql-rpt to gain access to either org-sql-1 or org-sql-2? (i.e. any risk that could be mitigated by using different service account credentials?)

Or is this just a generally bad idea for other reasons?

EDIT

The new instance will be hosting Database, Integration, and Reporting services (for now, at least).  No Analysis services.

The external user will have elevated database privileges, but no explicit instance permissions.  They won't be able to create logins, jobs, or reports, for example.


texts: Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

texts:
Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the nearest 100th, thus making the place marker on google maps inaccurate.

Any suggestions on how to always return the true value of the number ?


texts: SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

texts:
Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %)
This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has resolved the issue)
Issue start: 12:15
Issue end:   14:15 (after manual AG failover)

Server Info:
SQL Server 2017
32 logical processors (max DOP = 8)
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

Below metrics DIDN'T change noticeably before the issue vs. after the issue started

User Connections / sec ( avg. 200-300 )
Batch Requests / sec ( avg. 200 and lower )
Database Cache Memory ( ~ 150 GB )

Below metrics PEAKED considerably, which is NOT typical for this server (usually these are low):

CPU ( over 80 % )
Memory Grants Pending
Lock Waits/sec, Avg. Lock Wait Time, Deadlocks
Latch Wait Time
Granted Workspace Memory and Reserved Server Memory

Queries:
I did not notice change in workloads for this server when issue had started
Developers also confirmed that applications did their usual job and were running usual queries, no peaks in application load
During this "high CPU usage" issue, top 10 queries by CPU didn't look unusual
All the same top 10 queries that we usually see even when CPU is normal (10-30 %)
Problem:
The problem seemed to be in a couple of related stored procedures, that application usually runs 1-4 times / second, and those usually complete within 50 ms, but during the issue, anytime I've checked the sys.dm_exec_requests (also used exec ViewSessionsConnections 'running' https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql), there were sitting like 50-70 sessions from 1 application, all trying to complete above mentioned procedures, and it was slow
When looking at top 10 queries by Duration in a Monitoring tool, top 1 and 2 these were two statements from above procedures - they did not consume a lot of CPU, BUT had excessive waits (RESOURCE_SEMAPHORE, LCK_M_IS)
Usually these complete within 10 ms or less, executed 1-4 times per second and not causing any issues, and now these started having duration of 4000-8000 ms per 1 execution, which was the issue
RESOURCE_SEMAPHORE is absolutely NOT typical for this server, but during the issue it was among top waits
(RESOURCE_SEMAPHORE - Queries waiting for memory to be granted; total  135400234 ms in 2 hours;  avg 4174 ms )
Granted Workspace Memory and Reserved System Memory in SQL Server rocketed from 0 to ~110 GB during the issue

Questions:

What are your thoughts and experience on above ?

Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries? Because when looked at top 10 queries by CPU during the issue, CPU numbers looked normal / usual

How can it be that Granted Workspace Memory and Reserved Server Memory started consuming ~ 112 and 110 GB when the issue started, given that Max Server Memory is 180 GB and Database Cache Memory remained ~ 150 GB all the time? Did it over-commit memory or something like that ?

Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ?

How can issue be solved in a more surgical way, without doing manual failover to secondary replica? How can I calm down the query and bring it back to 10 ms ?
Plan needs to be dropped, or query recompiled, etc. ?
What is the best way to do it and monitor it?

Brent Ozar First Responder Kit or other diagnostic procedures - in which sequence should be executed during performance issues, to better understand what is going on ?


texts: Getting Error/Exception In GoldenGate Replication

texts:
I am planning to configure a simple GoldenGate configuration and want to implement an exception table that will store all the exceptions. I followed two links, neither of which work for me:

Troubleshoot Oracle GoldenGate Using Exception Handler
Creating a GoldenGate Exception Handler to Trap and Log Oracle Errors

My Extract Parameter File

extract ext9
userid ogg_user, password oracle
rmthost targetGG, mgrport 7809
rmttrail /u01/app/oracle/product/gg_trg/dirdat/np
table scott.emp;

Replicat File

replicat rep9
ASSUMETARGETDEFS
userid ogg_user, password oracle
include ./dirprm/EXCEPTION_HANDLER.mac
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
MAP scott.emp, TARGET scott.emp;
MAP scott.emp #exception_handler();

Exception Handler Macro

Created in ./dirprm/EXCEPTION_HANDLER.mac:

MACRO #EXCEPTION_HANDLER
BEGIN
, TARGET GGATE_ADMIN.ALLEXCEPTIONS
, COLMAP ( REPLICAT_NAME = @GETENV("GGENVIRONMENT", "GROUPNAME"))
, TABLE_NAME = @GETENV ("GGHEADER", "TABLENAME")
, ERRNO = @GETENV ("LASTERR", "DBERRNUM")
, DBERRMSG = @GETENV ("LASTERR", "DBERRMSG")
, OPTYPE = @GETENV ("LASTERR", "OPTYPE")
, ERRTYPE = @GETENV ("LASTERR", "ERRTYPE")
, LOGRBA = @GETENV ("GGHEADER", "LOGRBA")
, LOGPOSITION = @GETENV ("GGHEADER", "LOGPOSITION")
, COMMITTIMESTAMP = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;

This is the error I have been getting. I have tried/Googled a lot to get rid of this, finally posting it here for help:

  2016-06-09 17:18:12  ERROR   OGG-00212  Invalid option for MAP: TABLE_NAME.

I don't know what is causing the problem. I have tried all space issues found on Google and with other bloggers but no success.


texts: Any way to estimate the required space for a SQL dump file?

texts:
I want to import a Wikipedia XML dump file into MySQL. The uncompressed XML dump file is 65GB. Does this mean I should free up almost 64GB in the MySQL data path? Is there any way to estimate the required space?

Here is the sql schema:https://git.wikimedia.org/blob/mediawiki%2Fcore.git/HEAD/maintenance%2Ftables.sql