texts:
I want to use logshipping to transfer large databases (400-700 GB size) from Server (and storage) A to Server (and storage) B.

I tried this several times and it worked. But I have questions about some details:

While using LogShipping, the only Log-Backups that can be taken are the backups that the logshipping process creates. So other backup software or jobs have to be disabled in advance.

In case I setup log shipping, the initial fullbackup that is required, independantly wether I take and restore it manually in advance of the logshipping setup or let the logshipping setup create and restore it for me, will take a while, at least one hour or more, to be finished, and additional time to be restored on the secondary. I have already tried both options with success for smaller dbs.

My question is: If I set up logshipping for a large db and need to stay able to do a point in time recovery, what will happen in the time between taking the initial full backup and the first log backup taken after the restore of the full backup has completed? Is there something like a gap in backup? Before the setup of log shipping is completed, I guess there is no backup configured at all, so will I lose thios period of time? Or will the transactionlog just keep growing until the full-backup-restore part has completed and the first log backup happens?


texts:
Microsoft provides hosts two repos,

https://packages.microsoft.com/repos/mssql-server-2017-gdr-xenial/
https://packages.microsoft.com/repos/mssql-server-2017-xenial/

What does GDR stand for in that context.

You can find it on Microsoft sites but they don't explain it

  GDR update package for SQL Server 2016 SP1

You can see it here too.


texts: Is it possible to migrate Sharepoint 2016 and all it's databases from SQL SERVER2012 to AZURE?

texts:
we are in a process to migrate our website to azure.

we have those tons of sharepoint databases :

Is it possible to migrate all user databases + sharepoint to azure?

I could migrate some user databases to azure with no problem in the past ( bkp as data tier, and etc and etc ) but I've never done this with sharepoint databases.

I could found some questions saying it's not possible.

https://sharepoint.stackexchange.com/questions/241759/sharepoint-2013-on-azure-with-on-premise-sql-server
It says "ou cannot move to a Azure SQL Database".

https://sharepoint.stackexchange.com/questions/217477/sharepoint-migration-of-db-from-one-server-to-another-server
This one is from on premises to on premises.

https://docs.microsoft.com/pt-br/archive/blogs/sambetts/hosting-sharepoint-content-databases-in-sql-azure
"Important edit: this is currently not officially supported for SharePoint Server, even for Azure-hosted farms."

and then with all those questions and answers, I dont know how to proceeed.


texts: [sys].[dm_db_index_usage_stats] returns more entries in some databases

texts:
I am using the following query to get some indexes usage statistics:

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,NULL) AS PHS
    ON PHS.[index_id] = US.[index_id]
    AND PHS.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = PHS.[database_id]
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY [Page_Count] DESC

The issue is on some database the [sys].[dm_db_index_usage_stats] returns more entries for certain indexes. For example:

As you can see each entry is updated in different times on different date. Could anyone tell why this is happening?


texts:
Update 2020-08-04:
Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are easily handling a few billion rows in the table(s). The index-only scans are a life saver and it wouldn't be possible without.

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query plan output.
My table layout:
                                   Table "public.energy_energyentry"
  Column   |           Type           |                            Modifiers
-----------+--------------------------+-----------------------------------------------------------------
 id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
 prop_id   | integer                  | not null
 timestamp | timestamp with time zone | not null
 value     | double precision         | not null
Indexes:
    "energy_energyentry_pkey" PRIMARY KEY, btree (id)
    "energy_energyentry_prop_id" btree (prop_id)
    "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
    "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

The data ranges from 2012-01-01 till now, with new data constantly being added. There are about 2.2k distinct values in the prop_id foreign key, distributed evenly.
I notice that the row estimates aren't far off, but the cost estimates seem larger by factor 4x. This probably isn't an issue, but is there anything I could do about it?
I expect that disk access might be the issue, since the table isn't in memory all the time.
EXPLAIN ANALYZE
SELECT SUM("value")
FROM "energy_energyentry"
WHERE
  "prop_id"=82411
  AND "timestamp">'2014-06-11'
  AND "timestamp"<'2014-11-11'
;

 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

Any suggestions how to make this faster?
I'm also fine with just hearing I didn't do anything weird.


texts: VDI OpenDevice fails Mostly observed in SQL Server 2017 U19 and above on Linux

texts:
What are the changes Microsoft has made changes to SQL Server 2017 CU19 (February 2020) that is preventing the Plug-in for SQL Server (third-party )to protect SQL Server 2017 CU19?
Environment :
The version of OS: x86_64 x86_64 x86_64 GNU/Linux
Sqlservr
Version 14.0.3335.7
Build ID 8437c4d7ea0c407855fdf5e6d199b4f6a2b62c761c0cbbd72773d53348711e00
Build Type release
Git Version 5c7ee6c
PAL
Build ID bf2ab14a753fb4a15f020c1adcaf4b2476f6487bd265370ca52ee6941a70906d
Build Type release
Git Version 95da3ad
Packages
system.certificates 6.2.9200.25,unset,
secforwarderxplat 14.0.5005.60667
sqlservr 14.0.3335.7
system 6.2.9200.25,unset,
sqlagent 14.0.3335.7
ODBC Error Number:42000, MsgState=1, Severity=16, Servername= ProcName=, LineNumber=1, Message:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]BACKUP DATABASE
Most common Error is VDI OpenDevice fails
Also, note Testing shows any version CU18 or prior doesnt report the said error
Impacted link which needs to amended if there is an issue confirmed is here ->
https://support.microsoft.com/en-in/help/4535007/cumulative-update-19-for-sql-server-2017
Is the same issue as reported under SQL Server Version 2019 ( https://support.microsoft.com/en-us/help/4563007/fix-vdi-backup-fails-with-error-after-applying-sql-server-2019-cu2-cu3)


texts:
I just installed sp_Blitz and it has been running for an hour and still won't complete.  I am able to run sp_BlitzFirst and it takes about 3 minutes to run but does complete.
Any idea why it won't complete?
It finally failed after 1 hour and 11 minutes

There is insufficient system memory in resource pool 'internal' to run this query.

I have one small test database in Recovery Pending state, could that be the issue?
I have Activity Monitor open but don't see any category maxed out, Proc 32%, Waiting Tasks 0, DB I/O .2 MB/Sec, Batch Req. a few hundred.
I ran sp_Blitz; GO and also selected the common settings in the script at the bottom and tried that with the same results.
Environment specifications

sp_Blitz ver 6.4.
SQL Server Std 64 bit, 2014, SP2 Build 9600
128GB of RAM with max set to 116GB
Single server with about 25 databases, one large Dynamics database, about 300 GB.


texts: Can we disable binary logging for one specific session or user in AWS RDS

texts:
Currently we have a situation to disable binary logging for one set of queries on AWS RDS. We thought to set log-bin=0 for that specific session. As per Amazon, this is not possible. Is there any work around for us ?

We want binary logging to be working for the remaining activity that happens in parallel
to this session.
If needed, we can stop and restart slave.

Thanks in advance.


texts: what is the fastest way to restore MySQL database without using dump files

texts:
what is the best procedure to quickly restore the MySQL database without using dump files. can any body help me in this regard.
Thanks in advance.


texts: How I can monitor Amazon RDS Postgresql table size once the `hot_standby_feedback` is on

texts:
I have a postgresql Master-Slave replication scheme and because of query cancelation due to conflict with wal I need to set the following setting to each replica:

hot_standby_feedback = on

But because this can cause the tables on my master server to have overly large amounts of data that are about to be deleted but not deleted yet, I need to be able to monitor the table size in order to force manually an autovacumm policy on mergency situations (storage starvation or overprice because of storage size).

Therefore, how do I monitor the table size on my database? Also, how I can enforce the AUTOVACUMM in case of my tables having way too much size.


texts: Using composite indexes with date ranges

texts:
I have a table of rules that are activated depending on the date.

id - name - value - start_date - end_date
1  - ABC  - 10    - 2015-12-01 - 2015-12-31
2  - DEF  - 15    - 2016-02-01 - 2016-02-29

My SQL queries will mostly be

SELECT * FROM rules WHERE start_date <= '2015-12-05' and end_date >= '2015-12-05';

Does having a composite index consisting of start_date and end_date help or will worsen the query?

Edit: I'm using MySQL


texts: Mysql Server 5.6 Mysqld high memory consumption

texts:
I have MySQL Server 5.6 installed in a development machine with these specs:

RAM: 4 GB
Windows 7 Professional 64 bits
Intel Pentium CPU G630 @ 2.70 GHZ

Since I installed the MySQL Server, the process mysqld.exe is consumpting 433 MB.

What I want to achieve is to reduce this consumption as much as possible.

What I have tried

I read some posts about adding some parameters to the my.ini, but inside the MySQL directory I have a file called my-default.ini so I added the following properties inside:

[mysqld]

# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K
performance_schema = off

Also I ran these commands to flush the queries cache:

FLUSH QUERY CACHE;
RESET QUERY CACHE;

All these attempts have not reduced an Megabyte, it stills consumption 433 MB.

Thanks in advance


texts: Corrupted mdf file causing the backup job to fail

texts:
We have ran into a problem with one of our SQL server 2008 databases. It appears that something is corrupted in its datafile and this has caused our daily DB backup jobs to fail. The last successful backup copy taken was a month ago :(

Luckily the database is up and running  and the application is able to connect to it. The only problem is it doesn't allow the db to be backed up anymore.

When I try to take a backup it gives me "Read on e:\xxx\xyz.mdf file failed. reason 15105" error.
I have stopped SQL server service and tried to manually copy the mdf and ldf files but the system doesn't allow us to copy the mdf file. It gives a "Cannot read from the disk" error

I have ran the DBCC CHECKDB command to check the errors and below are the results

  1)    Unable to read and latch page (1:43515) with latch type SH.
  23(failed to retrieve text for this error. Reason: 15105) failed.

  2)    Object ID 567673070, index ID 1, partition ID 72057595461697536,
  alloc unit ID 72057595536211968 (type In-row data): Errors found in
  off-row data with ID 1788936192 owned by data record identified by RID
  = (1:44202:25)

I was thinking of running the DBCC command with REPAIR_ALLOW_DATA_LOSS but I am really scared of losing the data. If something terribly goes wrong we don't even have a recent backup copy to restore.
It is a huge risk for us to have a production DB without having the ability to back it up.

What would be the best way to repair the corrupted datafile without data being lost? do you recommend any third party tools?
Is there any other way that I can backup/take a copy of this database before starting to repair?


texts: How to difference a column name on the select statement which derive from diferent ancestor tables?

texts:
I need the name of a doctor and the name of a patient, both derive in the same last child table which is Person, and both have ancestor a Register table, so something like:
         Patient
       /            \
Register
       \                   Person(idPerson, Name)
          Doctor     /

So on the select statment how do I differentiate the Name from Person that stems from Patient versus the one that stems from Doctor?
Query would be something like this, but does not work.:
SELECT
    Person.Name AS DOCTORNAME
,   Person.Name AS PATIENTNAME
FROM
    Register
    INNER JOIN
        Doctor
        ON Register.Doctor_idDoctor = Doctor.idDoctor
    INNER JOIN
        Person
        ON Doctor.Person_idPerson = Person.idPerson
    INNER JOIN
        Patient
        ON Register.Patient_idPatient = Patient.idPatient
    INNER JOIN
        Person
        ON Patient.Person_idPerson = Person.idPerson;

So how do I get the select to make this difference out?


texts:
A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running.  The script created a new table and then inserted at least 70 million rows of data into the new table.  Now service.msc in windows 7 will not start MySQL as a windows service.  It gives error 1053.

How can I get MySQL to launch as a windows service?

Here is what I have done do far:

Deleted the ib_logfile* files
Deleted the myservername.err file
Changed the following settings in my.ini:
    innodb_additional_mem_pool_size = 100M
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 50M
Restarted the computer a few times, trying to start the MySQL windows service a few times

I notice the size of the ibdata1 file is 76,812,288 KB

I have thought to just delete the newly created table, which would reduce the size of the database by a huge factor and get things back to what they were before the bad script ran.  It would be nice if I could just drop the table without opening the mysql windows service, or somehow bring the database back to the state it was in just before the script started running, and then just restart the windows service without the damage that is causing it not to start now.  But I do not know how.  Can anyone show me how to get windows to start the MySQL service again?

I uploaded the myservername.err file to a file sharing site.  You can read it by clicking on this link.  Please understand that this error file continues to grow into hundreds of megabytes in size.  The file sharing site just has the first few megabytes.  I imagine it gets repetitive after that.


texts: RMAN backups, incremental, not compressed: estimate size?

texts:
We´re setting up RMAN to backup a database over NFS and ZFS, for which I have to create shares and mounting points (4 shares in my case).

I am trying to anticipate the size of the shares, which should be the maximum size of the backup. We´re going to proceed with an initial backup level 0 and then we´ll do daily level 1 backups.

Is there a mathematical formula for me to know the maximum backup size at the end, using x as the database size?

Thank you,

S.


texts: Login issues for the same SQL login on mirror server working fine on principal server

texts:
I will try my best to describe the situation, while we've been trying to figure this out:

We have this SQL Login [LA] which shows successful connection on principal server in the sql error logs [connection made from various apps]. This login shows successful connection on its mirror server as well.

Now, here is this another login [LB] which connects successfully on principal server but fails to connect on mirror server. [same application as above login LA connects]

Troubleshooting done:

run sp_help_revlogin on both servers and matched the hashed password for login [LB]. Thus that confirms password is same for this login in terms of SQL login for both principal and mirror.
SID's of login is same at both servers, so this should not be a problem as well.
Application uses same creds to connect on principal which is a success but shows login failed on mirror server.

Since something is making the login unsuccessful attempts, login keeps getting locked out.

What else can i check as a DBA before passing this as an application issue to the app owners?

Please help me troubleshot to find the root cause of this issue, thank you.


texts:
I am trying to improve the performance of my postgres instance in aws.

I am using aws rds db.t2.large

Here is my table structure

CREATE TABLE public.my_test

(
    test_index1 text COLLATE pg_catalog."default" NOT NULL,
    test_index2 text COLLATE pg_catalog."default" NOT NULL,
    json_dump json,
    time_stamp timestamp without time zone NOT NULL,
    meta_info text,
    CONSTRAINT my_test_pkey PRIMARY KEY (test_index1, test_index2)

)
WITH (OIDS = FALSE)
TABLESPACE pg_default;

The current time for taking to do the following operation is about 0.02 - 0.08 seconds

    cur.execute("DELETE FROM my_test \
        WHERE test_index1 = %s and test_index2 = %s", \
        (my_index1, my_index2,))

    cur.execute("INSERT INTO my_test ( \
            test_index1, \
            test_index2, \
            json_dump, \
            time_stamp \
            )  \
                 VALUES (%s, %s, %s, %s)",
        (my_index1_1,
         my_index2_2,
         json.dumps(some_json),
         "now()", )
    )

Each json dump less than 500kb

This code exist in the an ec2 that is same zone of my rds

When I load test it I load test with 30k rows and perform reads and write.

I am only using 1 out of 20GB of space and 1 out of 4GB worth of memory.

My cpu utilization is low for both ec2 and rds eg. 10% or less

I am not entirely sure if this is normal, but I want to achieve 100 writes per second but right now it is sub 20.

I also try to upgrade my rds but the qps did not improve

Can someone point me a direction of how to debug this, like is there some network setting or memory setting I can check that is bottlenecking in ec2 or rds.


texts: How can I troubleshoot issues with dynamic registration?

texts:
For testing I have been trying to install Oracle 11g on a test system with a single database several times with the aid of snapshots. This most recent iteration I have been having issues getting the database to register with the listener.

I am to understand that PMON, as long as the database is running, will attempt to register itself at regular intervals with a listener that is running locally on the same machine using port 1521. I have such a listener running but the service is not registered it seems.

lsnrctl status
.....
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.13.134)(PORT=1521)))
....
The listener supports no services

I can connect to the db using sqlplus / as sysdba and running select name from v$database; shows the DB in question. So between that tnsping and netstat -an I know the listener is at least running. alter system register does not appear to have an effect either. Not that it should matter since I should just have to wait a minute for it to work on its own.

I then went looking for any PMON related logs. It should have been in the trace folder ($ORACLE_BASE/diag/rdbms/database_name/SID/trace/SID_pmon_PID.trc) with other trc files but it was not there. I then used the following query to verify where it should be

select pid, program, tracefile from v$process where program like '%PMON%';
d:\app\administrator\diag\rdbms\prod\prod\trace\prod_pmon_1464.trc

Windows 2008R2 with firewall disabled for testing (not that it changed anything). Listener.log doesn't show anything of worry. Just evidence of me restarting the listener many times.

I am aware that I could configure a static listener with SID_LIST but I would like to understand why this is not working. This is the 4th time around that I have been doing this and I have not run into this problem yet.

I am basically stuck since I am not sure where else I should be looking.


texts: Existing DB2 connection does not work inside a script

texts:
I connected to the DB2 instance, and I can successfully verify that the connection exists:

db2inst1@hostname:/tmp$ db2 connect

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = DBNAME

However, when I run the following script

db2inst1@hostname:/tmp$ cat test.sh
#!/bin/bash
db2 connect

the connection seems to disappear:

db2inst1@hostname:/tmp$ ./test.sh
SQL1024N  A database connection does not exist.  SQLSTATE=08003

I wonder,

What is reason for that?
How to work in scripts with the existing connection?


texts: Incorrect syntax near 'BULK' for bulk INSERT in Azure Data Studio

texts:
I am trying to bulk insert a large CSV file delimited by tabs. I am on a Mac, and executing queries on Azure Data Studio (formerly SQL Operations Studio), since SSMS is not available for Macs. Here is the query:

BULK INSERT [kf].myTable
from '/Users/username/path/to/file.csv'
with(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

The error I am getting is:

  Msg 103010, Level 16, State 1, Line 1
    Parse error at line: 1, column: 1: Incorrect syntax near 'BULK'

I've looked at other suggestions and none of them have worked. Is it possible that BULK is just not supported by Azure Data Studio?


texts: How to get top 1 row for each of the state in USA

texts:
I have a list of states in table 1 (Statename, StateAbbreviation)
I have different city based records having StateAbbreviations in table 2

I want top 1 record per state from table 2

My result should contain one record for each of the states in table 1


texts: How can copy one column data of one table1 into other table2 column and other clumns table2 empty?

texts:
Here is my problem.I have two tables Stdd and result.

stdd columns:

id
fname
lname
gender
phonenum
city

result columns:

physics
chem
math
english
urdu
bio
total
id

I want that in result table the column id copy data from stdd column id, and other columns of result columns remain empty or null.

How can i do this please help. I try this but nor works for me

       update result set  id= (select id from stdd)

following error come

  Msg 512, Level 16, State 1, Line 1
             Subquery returned more than 1 value. This is not permitted when  the subquery follows =, !=, <, <= , >, >= or when the subquery
  is used as an expression.
             The statement has been terminated.

I want this kind of output.Here is 'result' table

I'm using microsoft sql server 2008


texts: What is the best relationship for a pair of tables that have optional dependencies but aren't really related?

texts:
I have two tables inspections and tasks. The inspection is allowed to have a single task but not many.
Inspections don't need tasks and really there is no direct relationship. However, I have a link in a grid that goes to the optional task. Also, there is really no relationship to an inspection for the task so it would seem wrong to have a InspectionId field. Also, you can see that this would start to get ugly because I have four other types that are related in the same way. Having a whole bunch of unused foreign keys on the task would be nonsense.
I can easily implement a TaskId on the context tables and forget about it but it seems wrong.
Lastly, I can add a join table and use a many to many but this sort of adds some overhead and complexity when querying. It seems there is no good answer. Deep down I really want to make all my relationships many to many and relate all my entities to join tables. It seems to simplify many challenges save for for querying data of course. That part of me wants to create views with pseudo foreign keys mapped through the view for querying purposes.


texts: Mysql History Trigger Auto Increment bug or problem

texts:
I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave.

When a record inserted master's main table, trigger adds new row to history table with last_insert_id of main table. But when I add manualy add to new record main table on slave,  trigger adds new record to history table with correct auto_increment value.

CREATE TRIGGER `maintable_versioning_after_insert` AFTER INSERT ON `maintable`
 FOR EACH ROW BEGIN
  INSERT INTO `maintable_versions` (`version_id`,`old_version_id`, `maintable_id`, `xxx_id`, `yyy_id`, `zzz_id`, `type`)
  VALUES
    (NULL, NULL, NEW.`maintable_id`, NEW.`xxx_id`, NEW.`yyy_id`, NEW.zzz_id,'NEW');
END

----Table----

CREATE TABLE IF NOT EXISTS `maintable_versions` (
  `version_id` int(11) NOT NULL,
  `old_version_id` int(11) unsigned DEFAULT NULL COMMENT 'old version id of row',
  `type` enum('NEW','UPDATE','DELETE') NOT NULL,
  `maintable_id` int(10) DEFAULT NULL,
  `xxx_id` int(10) unsigned NOT NULL,
  `yyy_id` int(10) unsigned NOT NULL,
  `zzz_id` int(10) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=103243678 DEFAULT CHARSET=utf8;

ALTER TABLE `maintable_versions`
  ADD PRIMARY KEY (`version_id`);

ALTER TABLE `maintable_versions`
  MODIFY `version_id` int(11) NOT NULL AUTO_INCREMENT;


texts: How does MOLAP SSAS default processing detect changes in the relational source?

texts:
When you set SQL Server SSAS to MOLAP, and default processing, it detect changes in the relational source and only loads those changes into the SSAS MOLAP storage at processing time. But how does it detect those changes in the first place?

Preferably answers will refer to some Microsoft documentation.


texts: How to combine date and time to datetime in SQL Server?

texts:
I just found the following code:

CREATE FUNCTION dbo.JoinDateAndTime
(
    @d datetime,
    @t datetime
)
RETURNS Datetime
as
    BEGIN
        declare @res datetime
        set @res = convert (datetime, Convert(varchar(10), @d, 104) + ' ' + convert(varchar(8), @t, 108), 104)
        return @res
    END
go

After seeing the answers to How to the get current date without the time part I'm sure that involving conversions to varchar can't be the best solution.

BTW I searched SO for this question, but only found a solution for mySql.


texts: Finding parameter names for binding stored procedure parameters

texts:
For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle databases, I am using it to test the code that I have written.

I have been able to successfully call DBMS_OUTPUT.ENABLE with the OraleCommand.BindByName flag set to true. However, that is being called without passing any parameters. I have also been able to call a stored procedure that I made myself by passing a reference cursor bound by name.

When I attempt to call DBMS_OUTPUT.PUT_LINE in a similar fashion I get the following message:

Error Details

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am specifying the bind name for the parameter as item according to how the procedure is defined in the 10g documentation for DBMS_OUTPUT. The parameter type for item is defined as VARCHAR2 and that is the type that is being passed to the procedure. When I set the OracleCommand.BindByName flag to false, I am able to call DBMS_OUTPUT.PUT_LINE with no problems.

Here is the code that I am using to run the query:

public bool ExecuteQuery(string procedureName, string[,] queryParameters, bool useReferenceCursor = false)
{
    bool returnStatus = false;

    if (ConnectionStatus() == false)
    {
        //message database connection was interrupted
        return false;
    }

    OracleCommand cmd = new OracleCommand();
    cmd.CommandText = procedureName;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.BindByName = true;

    cmd.Connection = databaseConnection;

    if (useReferenceCursor)
    {
        cmd.Parameters.Add("output_reference_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
    }

    for (int parameterSet = 0; parameterSet < queryParameters.GetLength(0); parameterSet++)
    {
        cmd.Parameters.Add(
            queryParameters[parameterSet, 0],
            OracleDbType.Varchar2,
            256,
            queryParameters[parameterSet, 1],
            ParameterDirection.Input
        );
    }

    if (ConnectionStatus() == false)
    {
        //message database connection was interrupted
        return false;
    }

    try
    {
        queryDataReader = cmd.ExecuteReader();
        queryHasRows = queryDataReader.HasRows;
        returnStatus = true;
    }
    catch (Oracle.DataAccess.Client.OracleException error)
    {
        switch (error.Number)
        {
            case 6550:
                Console.WriteLine("The stored procedure specified doesn't exist.");
                DatabaseErrorMessage("The stored procedure doesn't exist. Make sure that the procedure has been properly compiled and that the names match.");
                break;
            default:
                throw error;
        }
        Console.WriteLine(error.ToString());
        queryHasRows = false;
        returnStatus = false;
    }

    return returnStatus;
}

Just for reference, we have decided to pass all parameters to the procedures that we write as OracleDbType.Varchar2 and perform the type casting in the stored procedures that we define. The only exception to this would be reference cursors. I am also using Oracle's ODP.net for access since the Microsoft equivalent is obsolete.

Any feedback or suggestions would be greatly appreciated.


texts: move content from one table to another table and add a new value

texts:
I need to move content from one table to another table, this part is done:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`,`n_registo`)
SELECT nome,empresa_idempresa,cat_mwc_idcat,0
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id;

How I can give value to n_registo, this value is the same for each time the query is executed.

output of  SHOW CREATE TABLE insc_mwc

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) DEFAULT NULL,
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idwater`),
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Getting duplicate record errors like "Duplicate Entry for key 'PRIMARY', error code 1062" when importing a full backup into slave

texts:
We're restoring our slave with a full backup from our master, and getting a bunch of duplicate key errors.  Once the slave gets caught up, it does not throw any errors (only when it is behind the master). We replicate all of the database tables.  We use a MariaDB Galera cluster as the master, and just a single MariaDB instance as the slave.

These are the steps that are done:

Get the GTID of the master by checking the variable "gtid_binlog_position".  Save this value.
Take backup of master with the command:

mysql -u -p --routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database <dbnames> | gzip > /tmp/backup.sql.gz.

This backup takes around 20 minutes to complete.
Source the database into the slave.
Run RESET SLAVE ALL, and set the GTID position to the value taken in step one.  This is done with the command SET GLOBAL gtid_slave_pos = "<gtid from step 1>";, and then CHANGE MASTER TO ...

After this, I receive the "Duplicate Entry for key 'PRIMARY', error code 1062" errors.

These are the settings that are used:

 [mysqld]
 innodb_buffer_pool_size = 6G
 gtid_domain_id=100
 log-slave-updates=true
 open_files_limit=1000000
 innodb_large_prefix = on
 innodb_file_format = barracuda
 innodb_file_per_table = on

 [galera]
 binlog_format=row
 default_storage_engine=InnoDB
 innodb_autoinc_lock_mode=2
 log_slave_updates=1
 ## Below was added
 sync_binlog=1
 innodb_doublewrite=1
 query_cache_size=0'
 wsrep_provider_options="gcache.size = 5G"
 wsrep_retry_autocommit=4

I read through the post MySQL replication: 'Duplicated entry for PRIMARY key, but it doesn't seem like the solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?


texts: Sql Server Agent fails to run batch from desktop

texts: I’m using SQL Server 2008 and running a batch file from SQL Server Agent. The batch file is located in my desktop. When I try to run the job I get an error: “access is denied.” The batch file runs successfully if I run it manually. Also, the job runs successfully if I put the batch file anywhere in the C drive or F drive and run it from Agent. Any idea why the access is denied if the batch file is in desktop? The agent service account has the sysadmin server role in SQL Server. Thanks.


texts: Getting the "ORA-00911" error when I am trying to insert more than 1 row

texts:
Lets say I have the follow insert statements.

INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');

I am using Oracle Express Edition 10g and using the web interface to execute these instructions.

If I insert them one by one, they work OK but if I enter them all at once and try to execute I get the following error.

ORA-00911: invalid character

How I can fix this issue?

Thanks.


texts: Multiline UDF or Stored Procedure for SSIS Data Source

texts:
We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow.  Would utilizing a stored procedure produce any faster results when streaming data from one place to another?

Table Valued Function Very Slow


texts: Does AWS ever `vacuumlo` PostgreSQL RDS instances?

texts:
Link to the long story happened before (and reason to have) this question.

Does AWS ever vacuumlo PostgreSQL RDS instances?


texts:
I will have a "clients" table and a "users" table what i wanna do is restrict access to some clients for some users

My question is how should i do the table structure for this? One solution comes to my mind is defining a field in clients table and putting the user ids in that field who wont have access to that client (so i can use that ids to check/exclude from result) but i dont know if it is efficent way? I mean if i do such field i am thinking to define it as "blob" field as i wont know how long that field should be, but i have concerns using a blob field/putting ids in one field? Will query be slow?

Is there a better/common way? Or my way is completely wrong?


texts: Error while trying to create a database for SSRS

texts:
I have installed SQL Server Reporting Services 2017, and my database engine is developer edition 2017. Now, while trying to configure SSRS from its Configuration Manager I get this error

I have found some really scarce online resources about this error which does not solve my problem.

I need help in figuring out what I should do? Do I need another edition of SQL Server database engine?

Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64)   Jul 21 2018 07:47:45   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 10.0  (Build 17134: )

I've downloaded SSRS from here: https://www.microsoft.com/en-us/download/details.aspx?id=55252

clicking the link provided in Developer Edtion setup.exe

SSRS Version: 14.0.600.892, Native mode

Thanks for your time.


texts: Mysql date_add returns wrong row in python, right row in phpmyadmin

texts:
I am trying to get the first and last record from a month.  In phpmyadmin I ge the correct result.  In python I don't.  In fact python returns the same row for both of these queries.  First number is the table primary key for the rows.

SELECT *
FROM mcs
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) ,
                                       INTERVAL 25 HOUR ) ,
                             INTERVAL -2 MONTH)
  AND wot_id = 1010965979
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

SELECT *
FROM mcs
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) ,
                                       INTERVAL 24 HOUR ) ,
                             INTERVAL -1 MONTH)
  AND wot_id = 1010965979
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

phpmyadmin shows this row for the first query,

839037 1010965979 2015-04-01 00:00:00 437.36 0.00

and this for the second:

953868 1010965979 2015-04-30 23:00:00 479.67 0.00

using MySQLdb and python 3

What is wrong?  is this a bug in MySQLdb?  I would not think so as it just passing a SQL string to mysql


texts: How to update "sorting_order" column of other rows when changing one?

texts:
I need to set a custom order sequence for categories when adding new ones, and changing existing ones. I believe the code and sql query will be the same in both scenarios.
In my webshop_categories-table I have a so-column (sorting order INT) that I uses to determen which order the categories should be outputed to the viewer.

Here's a simplified example of how my categories-table looks like (so = sorting_order):

id  |   name   |  so
--------------------
1   |   Shoes  |  1
2   |   Hats   |  2
3   |   Bags   |  3
4   |   Coats  |  4
5   |   Rings  |  5

When adding a new category, without defining the sorting order, I would just submit a form, and run a query like this:

$so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
$ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);

(This will add a new category, and just put it at the end. In this case the so would be set to 6)

That's the very basic way of just adding a new category. Works fine. But in most cases, when dealing with categories, one need to put it in a specific order for presentation.

But usually when adding a new category, one would want to specify where this new category should be placed in relation to the existing ones. And that's when updating the so-column becomes a brain twister for me...

My simplified form for adding a new category:

<form>
  <input type="text" name="name">
  <select name="so">
    <option value="0" selected>At the end</option>
    <option value="1">At the top</option>
    <!-- php foreach loop through exising categories -->
    <option value="<?=so?>">after <?=category_name?></option>
  <select>
  <input type="submit" name="submit_add" value="Add">
</form>

<?php
if($_POST['submit_add']){
  if($_POST['so']==0){  //  put new category at the end
    $so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
  } else {
    $so = $_POST['so'];
  }
  $ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);
}
?>

I've been through the first scenario: Not specifying a sorting order.
But when it comes to using this feature, I need to update the so for the affected categories.
If I add a new category between Shoes and Hats I would use after Shoes in the selection list - which means that this new category should get the so-value of 2. This means that it will be necessary to do a +1 to the so for Hats and all categories above.

What would be a good way of accomplishing this?

A quite equal situation to this would be when moving a category from a higher to a lower so number. One would need to make space for the moved category, and bump the rest one up (do a +1 to their so). But only the ones in between the two positions ofcourse.

And it should be quite similar when doing it the other way around - Moving a category from a lower to a higher number. The only difference being that we now need to close the gap that we created between these two powitions.
If I moved Hats to after Coats - Hats would become 4, and the rest would need to be bumped one down (do a -1 to their so).

I've tried to do this by fetching the affected categories when using this feature, and update each one in a foreach loop. But I couldnt get it to work as expected for all scenarios.

Making a Copy of the all the rows (into a PHP array), Delete them, Create a new list with updated so, then Inserting them back was also a solution, but I dont think it should be neccesary just to make these changes... But that's one way I figured out that worked...

Any suggestion?


texts: SQL Server Failover cluster installation fails `RequireKerberos` to value '1'

texts:
Hi I am trying to create a SQL Server Failover cluster. Windows Cluster is configured and working as expected but now when I try to install SQL Server it throws the following error:

  OS:   Window Server 2008 R2 DataCentre x64 Bit
  SQL:  SQL Server 2008
  Enterprise Edition x64 Bit

I have seen some posts online an suggests that I need SQL Server 2008 with SP1. I can install SQL Server 2008 and then apply the SP1 but it would not let me even install it and throws the error.

Any suggestions or pointers in the right direction are much appreciated. thank you.


texts:
I have a table with a xml column.
Xml is similar to

<Root>
  <Row>
    <user>abc</user>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <user>vf</user>
    <Rowid>2</Rowid>
  </Row>
  <Row>
    <user>ert</user>
    <Rowid>3</Rowid>
  </Row>
  <Maxrowid>3</Maxrowid>
</Root>

Now below query return sl_no column and myxmlcolumn of rows containing xml column having values 'abc' or 'xyz' in node 'user'().Below query i am using similar to IN option of sql.

SELECT
    [mytable].[Sl_no],
    [mytable].[myxmlcolumn]
    FROM [mydb].dbo.[mytable]
    WHERE
        [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) return $x') > 0

I want similar kind of query which does same work as sql 'NOT IN' does. That is in my case i want rows not having values 'abc' or 'xyz' in node 'user'() in xml column.
So please help me on this.


texts: Grant privileges in other table than mysql table

texts:
I have been requested to create a back-end area for an existing website (on a provider's server), consisting of several tables and php pages.
This back-end area should not be accessible/visible to our normal website vistors, so a login system (php) has been developed.
I am also expected to be able to grant privileges (select, insert etc.) for those who CAN access this back-end area. However, I have no access to the mysql db on the provider's server.
Is it possible to create and use a separate table for granting privileges on my tables or can privileges only be set in the mysql db itself ?
Any suggestions/help welcome. Thx, LBox


texts: How to find out the maximum number of datafiles per tablespace in Oracle?

texts:
According to this Oracle document, the maximum number of database files per tablespace is "Operating system dependent; usually 1022".

I know there are queries for some of the other limits mentioned in that document (e.g. to determine the database block size: select value from v$parameter where name='db_block_size').

Is there a query to determine the maximum number of datafiles per tablespace?  (I tried select name from v$parameter where value='1022' but that returned no rows.)


texts: Delete from table rows where any of the column field is null

texts:
Is there a way to delete a row from a  table where any of the column field is null without specifying explicitly which column is null?

I am using postgreSQL.

Here's my relation schema:

  Column    |  Type   |                              Modifiers
  --------------+---------+----------------------------------------------------------------------
  id           | integer | not null default  nextval('aurostat.visitor_center_id_seq'::regclass)
  date         | date    |
  persons      | integer |
  two_wheelers | integer |
  cars         | integer |
  vans         | integer |
  buses        | integer |
  autos        | integer |

Thanks


texts: Update with distinct on violates unique key in postgres?

texts:
update schema.current_status a
set status       = stat.overall_status,
    created_date = stat.created_date,
    parent_id    = stat.parent_id,
    id  = stat.id
from (select distinct on (id) id,  parent_id, overall_status, created_date
from schema.daily
where parent_id
          =
      'abc'
     ) stat;

This is the query I am using, when I run just the sub query, I can see it is returning unique records only, but when I try to update the table, it says it violates unique key. My unique key is on parent_id, id


texts: Populate columns of table with other table that doesn't have relationship

texts:
Hello I have 2 tables in PostgreSQL: the 1st one

"Products"
id
price
factor1
factor2

the columns factor1 and factor2 they both are empty and I want to populate them with the data of another table

"Article"
id
factor1
factor2

I want to populate the columns where the id of Product is the same as the Article but the tables doesn't have a relationship to each other or FK so how can I accomplish this?

I've tried this:

UPDATE Products
SET factor1 =
  (SELECT factor1
   FROM Article
   WHERE Article.id = Products.id)
WHERE EXISTS
  (SELECT factor1
   FROM Article
   WHERE Article.factor1 = Products.id)

but I get this error:

  SQL Error [42P01]: ERROR: relation "Article" does not exist
    Position: 213


texts:
I currently have an Oracle 12c DW that has several data sources of varying types (MSSQL, Oracle, MS Access, PostgreSQL, etc.) being moved into it as part of our existing ETL process.  We have FME (safe.com) currently configured to do the bulk of it. For the most case this is functioning well.

However I have one data source that poses an interesting issue.  It is in production with no reporting/slave/replication strategy and it's resources/security prevents us from directly connecting to it. The only manner in which this data is available is through backups.  It is a MSSQL 2008 server with a weekly full backup and incremental backups daily between those weekly full backups.  I need to get this information into the Oracle 12c DW.  As far as I'm aware (currently speaking with FME support) FME isn't capable of directly reading backups and I'm likely unable to use FME short of restoring the backups to a different server and using FME to move from that intermediate server.

So I wanted to ask a 2 part question:

1) Can I restore a MSSQL full backup to an Oracle 12c database?

2) Can I restore incremental MSSQL backups to an Oracle 12c database?

And if this can't be done directly, is anyone aware of some workarounds?  I'd like to do this in a manner that can be scheduled (no manual work required) if at all possible.

My research shows that you can take some manual steps in Oracle developer to do this (might work for a one time migration, I'm unsure if I can schedule this process):
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

As a last resort, I'm pretty sure I can get an intermediate server with MSSQL on it that I can restore the backups to and then connect to it with FME.  However that has some costs associated to it (MSSQL license and hardware) that I'm trying to avoid for my client.


texts: Lock a table on purpose without returning results

texts:
In an update script I'm locking several tables.

BEGIN TRANSACTION
    SELECT Top 0 Null FROM TableA with (holdlock, tablockx)
    SELECT Top 0 Null FROM TableB with (holdlock, tablockx)
    ...

I want to suppress results for it in order to focus on real script results. Is there an elegant way to xlock a table without getting a result set?


texts:
I must choose between three possible DELETE methods in SQL Server. The table: RawData stays at around 500GB.

Here is the table:

CREATE TABLE dbo.rawData (
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT,
    MeasureId INT,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    DateEnergy DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

CONSTRAINT [PK_RawData] PRIMARY KEY CLUSTERED ([AreaId] ASC, [MeasureId] ASC, [DateEnergy] ASC)

CREATE INDEX IX_RawData_AreaId
  ON RawData (AreaId);
GO

CREATE NONCLUSTERED INDEX [IX_RawData_DateEnergy]
ON [dbo].[RawData] ([DateEnergy])
INCLUDE ([AreaId],[MeasureId])

GO
CREATE NONCLUSTERED INDEX [IX_RawData_MeasureId_DateEnergy]
ON [dbo].[RawData] ([MeasureId],[DateEnergy])
INCLUDE ([AreaId])

Method 1: Add a column IsDeleted type boolean.

Would require that this column be indexed as well (exclude rows
deleted by the SELECT)
Update this boolean column (update = rowlock)?

Method 2: Add a new table that will store rows that need to be deleted

Outer join between the two tables for the SELECT
No update necessary on RawData

Method 3: Use an existing column to detect rows to be deleted

Update the AreaID to a negative value to mark it for deletion (update
= rowlock)?
Use the existing suppression system that removes rows by batches of
10000

Constraints:

The deletion might need to be cancelled at any given time

Questions:

Which method would be the most efficent?
Are there any other methods not mentionned that might be better?

Update July 11th 2019

The number of rows being deleted depends on their collection date. So there could be millions or hundreds or none.


texts: Query Netezza to find all tables in views

texts:
I am trying to recreate the following query in netezza and am struggling:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = '<giveViewName>'
ORDER BY view_name, table_name

Any tips on how to do this?


texts: What contributes to the transaction size on a MySQL UPDATE?

texts:
Background
We're running a MySQL 8 INNODB cluster behind some Java services.  We received an error from the application because we'd exceeded the group replication transaction size limit (“Error on observer while running replication hook 'before_commit'”).  Our limit was set to 150Mb at the time.
Problem
Looking at the transaction involved I don't understand how it might have involved anything like 150Mb.
It involved an update to two tables
update my_table mt
inner join my_table_aud mta on mt.id = mta.id
  set mt.boolean_column_1 = TRUE,
      mt.boolean_column_2 = TRUE,
      mt.varchar_column = coalesce(mt.varchar_column, ?2),
      mta.varchar_column = coalesce(mta.varchar_column, ?2)
where mt.boolean_column_1 = FALSE
AND mta.rev <= ?1

which involved approximately 100 rows in my_table and maybe 200 rows in my_table_aud.  Plus one other simple insert to a different table.  The varchar columns were updated with around 10 bytes of data.
However the two tables involved in the UPDATE do both have a different longtext column, which wasn't updated. There would have been on average maybe 1MB in text per row updated in those columns.
The only explanation I can think of for us exceeding the transaction limit would be that the text in longtext columns contributed to the transaction size, even though they were not referenced in the update.
I searched for documentation on what contributes to the transaction size of a transaction in MySQL and haven't been able to find anything useful.
Please can someone help my understanding of how the transaction size limit might have been exceeded in this scenario?


texts: Clean up orphaned documents in MongoDB error

texts:
While trying to clean cleanupOrphaned  in my cluster

I am getting this error.


texts:
We have our clusters set to High Availability and manual failover. I have an alert to email DBAs whenever a failover occurs. What we would like to also see in the email is WHY and/or WHO. We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong. Right now the email is "The availability group database "TESTIN2" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required."
Thanks


texts: SQL Server slows down on one expensive query

texts:
I have a Sql Server 2012 with lot of CPU and memory.
When I run a query with a bad plan the server slow down and all other query, even of other databases, become very slow.

When I run the query:
- the CPU remains under 14% - so is not a CPU problem
- there are not grant memory pending - so is not a workspace memory
- non problems on disk I/O - so is not a disk I/O problem
- PLE is 8000 - so is not a buffer pool problem

I notice that the query use a lot a workspace memory (probably reaching the 20% limit) and free memory goes near to 0.

I'm stuck.

Could so one help me?


texts:
I have noticed dup’s generated in my mail system. some service was causing duplicate calls to my insert proc and generated multiple duplicate messages with about a 4 mili-second difference in a date time field i log on the message table message.CrDate.

Aside from the simple/ stupid way below is there a smarter way to use over or a cte to express the duplicates and their messageid? where the messageid and crdate are unique to each message?

SELECT mr.EmailAddress , m.subject, m.body, m.size, md.UserId, COUNT(*) TotalCount
FROM message m inner join MessageDetail md on m.id = md.MessageId
   inner join MessageRecipient  mr on mr.MessageId  = md.MessageId
where m.crdate > '5/23/2014' and m.MessageSourceId = 2
GROUP BY mr.EmailAddress , m.subject, m.body, m.size, md.UserId
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


texts: How does a column-oriented DBMS filter on multiple columns?

texts:
I'm learning how column-oriented DBMS / "columnars" work for OLAP situations.
Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to know the products sold in the shop A in a certain time range:
SELECT DISTINCT product FROM data
WHERE timestamp BETWEEN 1600010000 AND 1602678400
            AND shop = 'A'

This will be stored like this (admittedly, this is more or less an abstraction):
timestamp [1600000000, 1600000005, 1600000005, 1600000017, 1600000018, ... ]
shop      [A, A, B, D, C, ...]
product   [X153, S76D, TYA6, LKJ6, SDH7, ...]

For this query:

I totally get how we can achieve fast lookup by timestamp, since this column is sorted: with 2 dichotomic searches we can find the index for which timestamp=1600010000 and 1602678400. With less than 30 read operations of a few bytes, it's done, we have rowid_start, rowid_end (I don't know if it's still called rowid in the context of a columnar) that make the boundaries of this time-range. The key thing is that we haven't had to read megabytes of data, but just a few bytes.

Question: then, how can a columnar filter by shop = 'A'? Do we have to read each entry of the column shop in the range rowid_start .. rowid_end to test if it's A or not? This could potentially be hundreds of MB or GB of data.

TL;DR: once we have filtered by one column, how can a columnar do a second-column-filtering, without doing a FULL SCAN?


texts: Are these database indexes correct for my search

texts:
I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/

I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows that this is a Heap, because it doesn't have a clustered index.

Now, all of our searches on this table are done based on iAssetId and dtUTCDateTime. This is the Query:

Select top (1) *
from VehicleMonitoringLog
where iAssetId = 1
Order By dtUtcDateTime DESC

We don't search based on the PK, BUT we are inserting data into this table every few seconds.

My questions are:

Because we are inserting data every few seconds do I need to create a clustered index on the PK?

OR, should I create a clustered index on iAssetId and dtUtcDateTime like this?

CREATE CLUSTERED INDEX
[VehicleMonitoringLog_Asset_dtmUTCDateTime_ClusteredIndex]

ON [dbo].[VehicleMonitoringLog] ( [iAssetId] ASC, [dtUTCDateTime] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

EDIT: These are the 3 columns in Question:

I have added an image showing the PK and FK of the Table. Another search we do is something like this:

Select * from VehicleMonitoringLogs
where iAssetId = 1
and dtUTCDateTime > GetDate() - 1
and dtUTCDateTime  <  GetDate()
Order by dtUTCDateTime ASC


texts: postgresql - Advance replication slot as part of a transaction

texts:
Let me illustrate my use-case, I have a Python middleware doing this data transformation on the top of a postgresql replication slot, I would like to leverage Postgresql transactional capabilities.
I would like to "listen" to the replication slot Y and at the same time make some INSERTs to a table X not belonging in the publication of the replication slot Y.
I would like to know whether it is possible to "advance" the replication slot within a transaction containing the INSERTs to the table X.
From a quick research I found out that I can:

use pg_logical_slot_get_changes to advance the replication slot, what I do not understand is how pg_logical_slot_get_changes behaves within a transaction. Can I assume that if I have a transaction failing containing pg_logical_slot_get_changes that will not affect my replication slot offset? I have a hunch this is the case.
I found out that via psycopg2 I can use direclty the replication protocol directly and then acknowledge the receipt of a specific "replication message". I guess the question I have is similar to the previous one, whether the acknowledgement (via send_feedback) to specific messages can be put in a transaction so that if the transaction does not commit successfuly the acknowledgement is not "committed" in that sense.


texts: When creating a PostgreSQL database one can assign another role to be an owner of that database. What can the new owner do with this database? How is it compared to grants?


texts: Group permissions in postgreSQL not inheriting to table level

texts:
I am moving to a group role from a lot of individual users and have one table that needs to have permissions SELECT, INSERT only.

Lets say I have:

user1
user2
newgroup

Both of the users are in the group and both users have INHERIT as well as the Group has INHERIT on it.

I have added

GRANT USAGE ON SCHEMA testschema TO newgroup;
GRANT user1 to newgroup;
GRANT user2 to newgroup;

as well as

GRANT SELECT, INSERT ON testschema.testtable TO newgroup;

When I log in as one of the new users, I do not have permission to that table(ex: testschema.testtable)

I look at the PSQL \z and see for that table newgroup has ar permissions.

I am getting permission denied for relation testschema.testtable.  I do not want to create more of a mess by individually putting permissions on the table (it works if I do that currently).


texts:
Does any one have experience on migration from DB2 to MySQL ?

Please share with me.

I tried googling, but I have not received much information on this.


texts: Can one make MySQL cluster on a unique index rather than a primary key?

texts:
The MySQL docs states that:

  When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

But that's not the only possibility, you can cluster off of a unique index instead:

  If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

It would seem to follow that if I first create a unique index on a table, it will be marked as clustered; I can then create a primary key, and it will be nonclustered:

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

However, when I inspect the resulting table, my hopes are dashed:

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

Another question here on DBA.SE implies that creating an index as part of the CREATE TABLE script, rather than afterward, as a separate CREATE INDEX statement, may make a difference.  However, I get the same result.

Is there a way to force MySQL to cluster on a unique index of my choice, other than the primary key, or does this "fallback option" only apply to tables with no primary key at all?


texts: What MySQL configuration options require more than editing the my.cnf file and restarting the server?

texts:
One example of this is the innodb_log_file_size configuration option. As discussed here, there are a series of steps that starts an hour before shutdown and goes to after shutting down. However, this doesn't appear to be documented in the MySQL documentation for this variable and I don't see a list of variables that you may not be able to adjust by simply editing the configuration file and restarting the server.

Does such a list of variables exist? What variables do I need to potentially watch when tuning MySQL to ensure compatibility with existing binary backups?


texts: Locking And Blocking With Merge Replication

texts:
I am using merge replication in SQL 2012 with web sync.

When I insert records into the top tables in my filter hierarchy it causes a lot of locking on the tables, including the merge replication tables such as MSMerge_contents.

It means that no other process can insert into the same tables at the same time.

Because of my particular setup this is a problem. Is there a way to reduce the amount of locking / blocking that occurs in the merge replication insert triggers?


texts: What is an "operational master" with respect to entity-relationship diagrams?

texts:
I have a college assignment to create an entity-relationship diagram (ERD) for a database we're going to build. That's fine, but I have to include something called an "Operational Master" in the ERD. The lecturer's description is vague at best, and I can't seem to find any info about it online.

Basically speaking, we have to create an ERD for a game rental service with at least one "operational master". The only other reference to it in the lecture notes was to "add an index for the 'operational master'". It was never really explained during the lecture as far as I can recall. I get what an index is, but not what is meant by the "operational master".

Has anyone any ideas on what this might be?


texts: The MS documentation on FILESTREAM makes it sound like it should only be used for archiving unstructured data. Am I misunderstanding this?

texts:
I am building an application that will store small image files (probably less than 1 MB typically) associated with records. I am considering the FILESTREAM option in MSSQL, but I'm a little confused about the documentation.

The documentation at Access FILESTREAM Data with Transact-SQL states:

  You can use Transact-SQL to update the data in the file system file; although, you might not want to do this when you have to stream large amounts of data to a file.

If I need to update a file with a new version of it, what other option is there other than using the T-SQL "Update" command?

The documentation at Create Client Applications for FILESTREAM Data even goes further and says you shouldn't Insert or Delete data in situations where the filestream column can be null:

  Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application's performance to decrease over time.

If you shouldn't insert or delete files in a table where the filestream can be null, what do you do in situations where not all records will have a file but there's a lot of transactions (including inserting and deleting) going on with that table? I mean, how else are you going to insert data to later read without...well...inserting it?

It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

I know I must be missing something, but the documentation seems a little confusing to me.

Is there a better tutorial or documentation out there for FILESTREAM?


texts:
I am trying to first Concat two columns and then perform a search operation using CHARINDEX on that calculated column as below :

 SELECT CONCAT(FirstName, ' ', LastName) as FullName FROM Customer WHERE CHARINDEX('Martin', FullName, 1) <> 0

however, I am unable to use the calculated column (FullName) inside the CHARINDEX function.

The error I am getting is :

  Invalid column name 'FullName'.

What mistake am I committing here?


texts:
Just trying out sp_BlitzWho for the first time.  On one specific server it never completes but continues to consume CPU, as observed via sp_whoisactive.

I have played with the SQL generated and isolated it down to the "blocked" outer apply.  If I comment out the WHERE clause in the "blocked" outer apply it completes.  Or if I only execute either of the conditions in the WHERE it completes.

WHERE (s.session_id = sys2.spid
   OR s.session_id = sys2.blocked)

I don't really know how to take this further.  I did consider 2 outer applies rather then the one with the OR in the WHERE.

I am running sp_BlitzWho Version = '5.2'; VersionDate = '20170406'.

As Brent pointed out, this is not the current version from GitHub.


texts: Which tool to compare an Oracle database with a Postgresql database?

texts:
We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3.
Thanks to Hibernate, all tables can be recreated seamlessly.

However, the Oracle database is huge. We are going to write a small tool that will bring the data from one db to the other.

We want to check the quality of the migration with some kind of third-party database comparator.

Do you know a tool that can compare an Oracle database and a Postgresql database (schema + data) ?


texts:
We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of the master/multiple-slaves kind i.e. db1 as the master, and db2 and drdb as the slaves. drdb is activated only when both db1 and db2 are down i.e. disaster recovery, business cont. sort of thing. Our application also has a rudimentary failover 'feature' in which it automatically connects to the next available db server in case the default active db server i.e. db1-to-db2-drdb, is inaccessible.

I am currently considering the idea of applying a master-master replication between db1 and db2, and making drdb as a slave of db1. In this setup, I'l make db1 as the default active db server (performs all writes and read operations) and db2 the passive one (backup db server). When db1 goes down, db2 becomes the active db server, and dynamically change the master-host of drdb to db2. And so on.

Is this a good idea? What is a better replication setup for this kind of environment? Please give some suggestions.

UPDATE 1:

I also would like to add that these databases are already existent, and I've read somewhere the possibility of key-collisions/error in this kind of setup, what modifications in our current tables/databases configuration should we made before we setup this kind of replication?

Thank you very much!


texts: how to drop oracle 12c instance on linux

texts: I have created an Oracle 12c container db instance.  I need to drop the  instance. What's the most effective way to drop instance?


texts: My mysql_slave got stuck on an error and built up to being 200,000+ Seconds_Behind_Master. Should I rebuild it and start from scratch, or let it catch up by itself?


texts:
Basic background:  I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package.

One Solution:  One option seems to be that we can install just SSIS on the app server without needing to install SQL Server itself.  The app would be able to run a package while referencing the DB itself on a separate server.

Question:  Would this require licensing SQL Server on the app server, even though the core engine is not part of the installation?


texts:
I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.

I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work.

Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either.

Thanks


texts: How to let particular logins to work on the secondary replica only?

texts:
We are configuring an availability group with a readable secondary replica.
A login can connect to the secondary replica using "ApplicationIntent=ReadOnly" in its connection string. But we came across a problem that could make our headache in future.

If the secondary replica is unavailable due to any causes when the login establishes connection, this login will be directed to the primary replica whatever it's using "ApplicationIntent=ReadOnly" or not.

So, my question - is there any way to ban particular logins from connecting to the primary replica if they configured for working with the secondary one?

I mean if the primary replica isn't able to direct the connection to the secondary one the connection should be closed instead of going on working on the primary replica.

  Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64)
    May 19 2018 09:41:57    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server
  2016 Standard 10.0  (Build 14393: )


texts: How to Get All Server Side Traces in SqlServer from SSMS, stop it from SSMS and delete the log files

texts:
How to Get All Server Side Traces in SqlServer from SSMS(Active,Inactive), stop it from SSMS and delete the log files.
I need all traces saving into file as well as table.

I tried SELECT * FROM fn_trace_getinfo(DEFAULT). Some times few parameters are NULL. What does it mean ?


texts:
Is there a way to get a list of tables and views that a specific user can see, without logging in as that user? Initially, I tried something like

select distinct p.table_name as value
from dba_tab_privs p
   join dba_synonyms s on
      p.owner = s.table_owner
      and p.table_name = s.table_name
      and s.owner = 'PUBLIC'
   join all_objects o on
      p.owner = o.owner
      and p.table_name = o.object_name
      and o.object_type in ('TABLE', 'VIEW')
where
   p.privilege = 'SELECT'
   and p.grantee = :user
order by p.table_name

However, while this would show tables and views that a specific user had been explicitly granted select on, it didn't seem to accurately reflect the full range of visible tables and views a user could see upon connecting to the database. For example, one user I tested had a little over 2000 tables and views visible when selecting on all_tables and all_views, but only reflected 300 records using the above query.


texts: SQL Managed Instance Restore to lower environments

texts:
I was trying to find an article or design pattern to fit my use case but I cannot find one.
My use case: I have a Production Subscription with a SQL Managed Instance that holds all production data.  Then I have a Dev/Test subscription that has one SQL Managed Instance for Dev and Test environments.  From what I'm reading I can't use PITR to automate the restore to lower environments if they are in a different subscription.  COPY_ONLY might be able to work if you set a custom TDE key?  I'm fairly surprised no one has written an article on how to accomplish this either, it seems like a very common design pattern?  Does anyone have advice on how this might be accomplished without decrypting the db?
I found this article but it doesn't really explain the design pattern for restoring MI to lower environments in a different subscription. Cross Subscription PITR


texts: How to relate 2 tables to a third when the first 2 tables have a relationship?

texts:
For once, I find myself at an utter loss in terms of coming up with a good question title.

Consider the three following tables. Each has additional columns, but only the columns relevant to the relationships between the tables are mentioned:

Table a has a primary key a_id
Table b has a primary key b_id and a column a_id which is a foreign key to a (one-to-many relationship)
Table reference has a primary key ref_id

reference is a list of sources that are cited to substantiate information contained in the other 2 tables. As such, I need tables to relate them together. Both a and b have many-to-many relationships with reference, but there is an additional complication: a footnote number. This footnote number must be unique for each a and reference pair, but it is only required when a relationship between a b and the same reference exists. (Of course, that b must be related to the same a.) The best way I have come up with to represent this relationship so far is a pair of tables:

bridge_a_reference:

Columns: a_id, ref_id, footnote_num (nullable)
Primary key: a_id, ref_id
Foreign keys: a_id to table a; ref_id to table reference

bridge_b_reference:

Columns: b_id, a_id, ref_id
Primary key: b_id, ref_id
Foreign keys: b_id, a_id to table b; a_id, ref_id to table bridge_a_reference

This ensures data consistency. There can only be a relationship between b and reference if a corresponding relationship between a and reference exists and b is related to the correct a. It also ensures that the footnote number will be consistent for all a/reference pairs. However, it's redundant, storing the relationship between a and b a second time (though enforced to be consistent, at least), and it doesn't ensure the footnote number is required for the b/reference relationships.

I've tried rearranging the tables in my head a few times, but everything else I can come up allows some kind of inconsistency (b to reference relationships without the corresponding a to reference or b to a relationship or inconsistent footnote numbers for the a/reference pairs.) How can I better structure my bridge tables to eliminate redundancy and ensure the footnote requirements? Is it even possible?


texts: DROP DEFAULT on psql restore fails because column not existing anymore

texts:
A dump creates a command to remove default values like:

ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT;

When on restore the column with the default value do not exist anymore, the restore gets an error:

ERROR: column "gid" of relation "simulation" does not exist

I solved my problem by deleting the table first manually, but I wonder if there would be a better sollution.

The scenario looks like this

The table "test.simulation" before creating the dump:

thebase=# \d+ test.simulation
                                                              Table "test.simulation"
   Column   |              Type              |                              Modifiers                              | Storage | Stats target | Description
------------+--------------------------------+---------------------------------------------------------------------+---------+--------------+-------------
 gid        | integer                        | not null default nextval('test.simulation_gid_seq'::regclass)       | plain   |              |
 objectid   | numeric                        |                                                                     | main    |              |

I make a dump of the table like this:

pg_dump --clean --if-exists -hlocalhost -p5432 -dthebase -Upostgres -ttest.simulation -Fplain -f/var/lib/postgresql/backup.sql"

Then I remove the column "gid" from the table "test.simulation". And because I shouldn't have done it I recreate it:

psql -hlocalhost -p5432 -dthebase -Upostgres --single-transaction -v 'ON_ERROR_STOP=1' -f/var/lib/postgresql/backup.sql"

... and the mentioned ERROR occurs. Because it checks if the table exists, but it does not have something like a DROP DEFAULT IF EXISTS option.


texts: MySQL: Storing calculation results that can be updated

texts:
I am currently saving the results of a costly operation in a database for later retrieval within an API. The operation itself takes around 0.5-1s depending on the input data. To be able to shortcut the calculation (which is possible in most cases), I am saving the result in multiple tables.

A) The generic table that saves the occurance of such a calculation

B) The table that saves a number of results that are related to A)

C) The table that saves some very detailed results in some occasions, related to B)

New recently a requirement was added where we need to update the results of the calculation under some circumstances. I added keys that reflect the uniqueness of a calculation and am now doing a INSERT ON DUPLICATE KEY UPDATE. However the write times to the database (under some load) are now essentially longer than the calculation itself.

What options do I have?

Migrate A, B, C into one big table that is easier to update
Instead of updating adding new rows to the table and using only the latest data. This will most likely cause some problems down the road with the amounts of data.
???

Thanks for your help!


texts: What's the smartest way to find the scale of an unrestricted numeric type?

texts:
By laziness and convenience I integrated quite a lot of data from a partner in an unrestricted numeric type in my PostgreSQL database.

However it now seems that depending of different shipments from this partner the scale of the numeric varies from zero to over 20 decimal places (or scale) which don't make a lot of sense and probably consume quite a lot of storage for no good reason. So I would like to restrict my numeric field to a reasonable scale.

However because my partner don't provide any recommendations I would like to identify the occurrence of each scale in my data-set to potentially identify a reasonable middle ground between 0 and 20 (zero obviously not being an option and given the 17 Million row-count, aggregated sums ARE going to be impacted by my decision).

It will also be needed during following rounding to avoid actually increase the scale of values that were shipped with a scale of zero.

Long story short, what the nicest way to compute that for a specific number stored as an arbitrary numeric?

Best I could come up to is that, but is there a more elegant way to do that without converting to text?

SELECT
  my_numeric,
  COALESCE(
    char_length(                            -- Finding size of string extracted by a...
      substring(my_numeric::text,'\.(\d*)') -- regexp to return all digits right from '.'
    ),                                      -- but if scale is 0 substring return NULL
  0                                         -- so I handled this inside a COALESCE
  ) AS my_numeric_scale
FROM
(VALUES
(0.1::numeric),
(0.12),(0.123),
(0.1234),
(0.12345),
(0.123456),
(0.000001),
(0.100000)
) foo (my_numeric)


texts:
I want to schedule function in Oracle.

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'aud_clear_fun',
                            start_date      => sysdate,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            end_date        => null,
                            enabled         => true,
                            comments        => 'Created By: MK; Truncates aud$ table');
end;

I have tried to indicate several other values for job_action, for example:

declare
my_var number;
begin
select aud_clear_fun into my_var from dual;
end;

But doesn't work. Can you provide me with the right syntax of scheduling function?

Thank you.

--aud_clear_fun

create or replace function aud_clear_fun
return number is
begin
   delete from a;
   return 0;
end;

--Job

begin
  dbms_scheduler.create_job(job_name        => 'aud_clear',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'begin
                                                 mari_dba.aud_clear_fun();
                                                end;',
                            start_date      => sysdate,
                            end_date        => NULL,
                            repeat_interval => 'freq=daily; byminute=0; bysecond=0',
                            enabled         => true);
end;

--Execution

begin
  dbms_scheduler.run_job('aud_clear');
end;

--Error

ORA-06550: line 2, column 50:
PLS-00221: 'AUD_CLEAR_FUN' is not a procedure or is undefined
ORA-06550: line 2, column 50:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

View program sources of error stack?


texts: EAV structure explained in Layman's terms

texts:
I've read several articles online that explain how EAV structures work, but there are different interpretations of them.  Some articles mention that it's a representation of a data model metadata (or schema).  Other articles describe an EAV structure as being an ontology.  I picture an ontology as either a conceptual model (showing relationships among "things" in the world), or class instances to the actual data in a database.

Can someone explain what each table in this model represents in Layman's Terms and how one would use this EAV data model?

I didn't see this as a representation of a data model, because [Object] is not directly linked to [Attribute].  You need an actual value to relate those two.  So there's nothing to enforce that you could use a [Attribute] A with [Object] O if A is a field/column of table O in your data model.  Now I did see the RI tables.  I assumed that meant "Referential Integrity".  But if we're talking about ontologies, then we could call it relationship identity.  I'm merely referring to the relationship or predicate when talking about an ontology triple--meaning Subject/Predicate/Object.  Note, that the joins are not connected with attribute, which would represent more of a relational data model FK/PK relationship with table columns, so I expected it to be connected to Attribute, not Object.  It's connected with [Object].

Please shed some light on this if you're able.  Maybe I'm thinking too much into this.  If you have a different EAV model that's useful, let me know.

Reference: http://eav.codeplex.com


texts:
This is a problem I often get stuck in and I hope there is a solution.

Let's build an example:

Table products: product_id, product_name
Table Prices: product_id, valid_since, Price

Lets say I would like to know the sum of all prices of all products (seems senseless, but it is just for illustrating the problem in an easy way)

Now you can imagine, prices will change with time, so the only price that matters is the price with the highest value of Prices.valid_since of course

SELECT SUM(Prices.price)
FROM products
INNER JOIN Prices
ON products.product_id=Prices.product_id

will result in something absolutely senseless, so how could it be possible?


texts:
I'm definitely a Database newbie and I'm trying to get SQL Sever Express set up so I can create and play with a database. I managed to install SQL Server Express 2012 before and It allready had the Server name populated. I was able to go in and use commands to create a database and then play with queries.

My laptop (which had it set up correctly) has since died and I need to set it up on my desktop.

Before, the server name was Name-PC/SQLEXRESS. When I try to use that this time though it acts like that is not correct.

I also installed LocalDB because I think that's what I need...

I know this might be a bit vague but I'm at a loss to how to get this set up so I can create the database.

Thanks in advance.


texts:
Recently my wordpress site is having troubles and is stopping from working due to heavy load in the mysql processes. I need to know what is causing all this traffic and how to narrow the issue so I can troubleshoot the error. I ran some commands and I am getting this results

show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 143   |
| Connections              | 17736 |
| Max_used_connections     | 198   |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 6     |
+--------------------------+-------+

mysqladmin status
Uptime: 3711  Threads: 3  Questions: 17872313  Slow queries: 13  Opens: 344
     Flush tables: 1  Open tables: 335  Queries per second avg: 4816.036

and sometimes the cpu consumption by mysql reaches the 300%


texts: What are the default settings for accessing MySQL remotely?

texts:
What is default settings in MYSQL for Remote access?

In CentOS the file /etc/my.cnf does not have skip-networking or bind-address. MySQL Version is 5.5, does it mean the server is enabled for remote access?

MySQL restart failed after adding skip-networking and bind-address=127.0.0.1 in my my.cnf file, so how can I disable remote access?

my.cnf

[mysqld]
skip-networking
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
default-storage-engine=MyISAM
long_query_time=5
log_slow_queries=ON
thread_cache_size=32
key_buffer_size=64M
table_open_cache=1000
max_connections=300
query_cache_type=2
query_cache_limit=32M
query_cache_size=160M
max_heap_table_size=256M
tmp_table_size=256M
wait_timeout=360
interactive_timeout=360
max_allowed_packet=16M
sort_buffer_size=8M
innodb_buffer_pool_size=256M
innodb_log_file_size=64M


texts: Proper creation of a database with categories that have sub-categories

texts:
Preface: I have for the most part never worked with databases, and am new. So my terminology may be incorrect at times.

I was wondering what the correct procedure would be for creating a database that has objects with multiple objects contained in them.

For a practical example, I was thinking if I were to make a database with separate meals, and also have the ingredients within the meals, how should I go about doing this? I was thinking it might look like this:

Meals  | Ingredient  | ~
------------------------
Chili  | Ground Beef | ~
Chili  | Beans       | ~
Chili  | Corn        | ~
Burger | Ground Beef | ~
Burger | Tomato      | ~
Burger | Lettuce     | ~
Burger | Bun         | ~

(the tilde represents extra columns, maybe calories, unit price, etc)

I was thinking this might be good, but I wouldn't be to sure how to group the meals. Would I have to make some query that looks through the meals column, finds similar names, then groups them together?

Not sure if there would be any better way of storing this type of information?


texts: How to import data from a dump file to a specific schema in postgres?

texts:
Let us assume there's a database name myDataBase. There are four schema in this database named

1: schema_1

2: schema_2

3: schema_3

4: schema_4

I have created a dump of schema_1 from this command pg_dump  myDataBase --schema=schema_1 > schema_1_dump

How to import schema_1_dump to only schema_3 but not to any other schema


texts: How to restrict SQL server profiler to show my symmetric key password while executing query from C# code?

texts:
I used symmetric encryption to encrypt sensitive data in my application. I don't want anyone see my password for decrypting the data. But system admin can able to see symmetric password in SQL Server Profiler.

How can I stop them ?

Profiler Result :

exec sp_executesql N' OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = ''admin@123'' ;


texts:
I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more than 6 hours. How can I optimize here?

query = """ select grid_id from grid into ##grid_temp
            where
            ....some logic.....

           set rowcount 10000
               while 1 = 1
               begin delete from grid_info where grid_id in (select grid_id from ##grid_temp)
               if @@rowcount = 0
                   break
           end
           set rowcount 0
         """
db_conn.execute_query(query);


texts: SQL Server and updating windows firewall rules

texts:
Any ideas how should I proceed if I would like to  update the firewall rules (essentially add ip-s to existing firewall rule).

The step of exporting ip-s to text file and then using command line to add/edit rules seems like a extra  step that requires some sort of automation as well.

Basically a ban list - it would currently only ban the account itself but I would like to use the associated ip to add it to the firewall rule whenever a new ban is added (trigger to check the update is a way to go) - but I'm not sure how to incorporate firewall update with SQL Server.

Would a trigger allow to run a command line command line function lets say basic "ping google.com" to start and test if it works.

like

BEGIN
SELECT i.ip FROM inserted AS i
INNER JOIN deleted AS d
ON i.ip = d.ip
"now do the  command line tricks and pass the i.ip as a parameter like " ping i.ip
END

So how should i proceed about the command line part?

SQL Server 2008 R2 sp2  windows 2008 server.


texts: Query performance degraded after upgrading Postgres

texts:
I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I tried rebuilding all indexes, vacuuming, and all usual Postgres low-hanging fruit, but performance is still terrible.
This is the query:
SELECT id, activity_count
FROM user
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true
ORDER BY activity_count DESC LIMIT 101

In other words, find all active users matching the given account number, and sort from most active to least.
This query takes about 5 minutes to return just 2 records. Something's not right.
The column search_index is a tsvector storing all the keywords from the table's various text fields (just things like account_number, name, etc).
I have a GIN index created for this column with:
CREATE INDEX user_search_index_gin
    ON public.user USING gin
    (search_index)
    TABLESPACE pg_default;

I also have an index for the active column with:
CREATE INDEX user_active
    ON public.user USING btree
    (active ASC NULLS LAST)
    TABLESPACE pg_default;

And I have an orderd index for the activity_count with:
CREATE INDEX user_activity_count
    ON public.user USING btree
    (activity_count ASC NULLS LAST)
    TABLESPACE pg_default;

Yet when I run EXPLAIN, I get:
"Limit  (cost=0.56..11443.66 rows=101 width=1552)"
"  ->  Index Scan Backward using user_activity_count on user  (cost=0.56..36010185.91 rows=317836 width=1552)"
"        Filter: (active AND (search_index @@ '''1234567890'':*'::tsquery))"

Why is it only using the user_activity_count index and not the more efficient GIN index? How do I fix this?


texts: Why does formatting a time with AM/PM specifier return null?

texts:
I have a query where a formatted expression is returning null:

declare @FormatString nvarchar(max) = N'hh:mm tt'
select M_STARTTIME as [Memo Time 1]
    , convert(time,M_STARTTIME) as [Memo Time 2]
    , format(convert(time,M_STARTTIME), @FormatString) as [Formatted Memo Time]
    , convert(time, getdate()) as [Current Time]
    , format(getdate(), @FormatString) as [Formatted Cur Time]
from MEMO
where M_STARTTIME is not null

M_STARTTIME holds an nvarchar field with values such as '10:27', '13:01', '8:25' and null. In this query:

[Memo Time 1] displays the string field
[Memo Time 2] displays the memo time as a time field correctly
[Formatted memo time] returns a null - for all rows
[Current Time] shows the current time
[Formatted Cur Time] shows the current time formatted using the format specifier.

According to MSDN, FORMAT returns null for errors other than a culture that is not valid. For example, null is returned if the value specified in format is not valid. I have not been able to identify where the error is though (I'm assuming i've missed something really obvious, or the issue is something really subtle).

Converting the nvarchar to a time should not be the problem, the time displays correctly in column 2. Formatting using the time specifier should not be the problem, since I use the same time specifier on another time value.

Interestingly, according to example D in the MSDN link (time values) that time format specifier should cause an error because the ':' is not escaped. If that is the case why is [Formatted Cur Time] working? Also, if I escape the format specifier with:

declare @FormatString nvarchar(max) = N'hh\:mm tt'

then the [Formatted Memo Time] still returns null and the [Formatted Cur Time] still returns the current time.

dbcc checktable (MEMO)

...identified no issues

What is going on?


texts: SSMS Cannot show the editor for this task (Notify Operator Task)

texts:
I'm getting an error message whenever I double click on a Notify Operator Task in our Maintenance Plans in SSMS, which says "Cannot show the editor for this task."

  Property IsMailHost is not available for Database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Database mail is configured and working OK.

I'm using SQL Server 2016 (13.0.4411.0) and SSMS 13.0.16106.4.

Interrogating the IsMailHost property via PowerShell seems to indicate that the IsMailHost property is true.

$db = SqlServer\Get-SqlDatabase -ServerInstance sqlserver1 -Name msdb
Write-Host $db.IsMailHost

The above script returns True.

If I try to open a Notify Operator Task on our old SQL 2008 server, with SSMS 10.0.5538.0, it works fine, but with SSMS 13.0.44110.0, I get the same error message.  I'm beginning to think it's a fault with this build of SSMS rather than a configuration issue with the server.