texts: Clear out remnant LDF log files of Sharepoint DB not visible on SBS 2008 Domain Server?

texts:
Typical ways for dealing with LDF files that take up a lot of space, recommend these processes to shrink/ eliminate the drive space being occupied.

http://www.sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ and http://geekswithblogs.net/RogueCoder/archive/2008/06/03/122588.aspx

As an example of following these steps for SBS Monitoring database visible in Microsoft SQL Server Management Studio Express GUI whose path location on disk is: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data (this path not shown in screenshot)

I followed these commands on the GUI:
DB: SBSMonitoring > Tasks > Delete > Log files and gain about 1-2 GB space.

One the other hand, I find the Sharepoint related DB files under path (shown in screenshot):
C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data
But, this database is not visible in Microsoft SQL Server Management Studio Express GUI.

I've looked at several answers and tutorials on how to clear out the LDF via GUI and SQL commands, but that is assuming the DB is visible.

What steps can I take to properly clear out the LDF log files for this DB?

Do I have to first add/ connect to it via the SQL GUI?
Or clear out without adding via SQL commands?
Or clear it out with simple file delete in explorer {not recommended from what I read most places}?

This is what I'm reading so far, but haven't found a way to add/ access that DB via those Files and then use above mentioned steps & guides.

Screenshot of files, location & DBs in Microsoft SQL Server Management Studio Express GUI:

Update: Adding more information about SQL instances running based on answer, and requesting update to answer on how to add/ access & flush this DB instance.


texts:
I am facing a performance issue with an update statement. It takes too long to update, but when I perform a select on that updating table it completes very fast.

UPDATE mo
   SET mo.RNCNEID    = ts.RNCID,
       mo.SiteNEID   = ts.SiteID,
       mo.SectorNEID = ts.SectorID
FROM COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR AS mo
 INNER JOIN COPS_UMTS_HUAWEI.Aggregation.tsector   AS ts
     ON mo.rncname      = ts.rncname
    AND mo.cellid       = ts.cid
    AND ts.VendorID     = 6
    AND ts.TechnologyID = 2
WHERE mo.RNCNEID IS NULL;

After running the below query:

SELECT COUNT(*)
FROM   COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR WITH (NOLOCK)
WHERE  RNCNEID IS NULL;

...it completes very fast.


texts: A column value depends on the values of two other columns, of which exactly one is nullable

texts:
In the Person table, either the Citizen_IdentityID or the Foreigner_WorkPermitID will contain NULL marks.

The PersonID_ID holds either the value of the Citizen_IdentityID or Foreigner_WorkPermitID (whichever that does not retain a NULL mark).

In other words, PersonID_ID is dependent on the value of either Citizen_IdentityID or Foreigner_WorkPermitID, and there are other attributes (eg. IDColor) that depends only on PersonnID_ID.

How should I structure the design for normalization?

The above entities are just a simple substitution of the actual use case.


texts: Client to use the slave when the master is down in MySQL replication

texts:
Is it possible for the client to use the slave when the master is down in mysql replication?

I have already setup a simple mysql replication having a master and a slave.

The client connects to the master to access the database, any changes made to the master server is replication onto the slave. How shall I configure that when the master server is down, the client automatically connects to the slave server?

I am using MySQL Server 5.1 and MySQL workbench 5.2 CE. I edited the my.ini files for both master and slave.

The client uses a J2EE application.

What additional commands must I input in the master my.ini file to make the changes?

Also is there a way to increase or decrease the time interval between replications?


texts: Is there a way to programmatically get the version of a .bacpac file without unzipping and inspecting the xml?

texts:
The situation is that we get a copy of the latest production bacpac, then restore it locally when setting up our dev environment. During the setup, we do an explicit check for the DacFX dll but target a specific version to warn the user if they are missing the required DacFx installation.

$DacPath = "C:\Program Files\Microsoft SQL Server\130\DAC"
// ... code to check x86 path if x64 not found and throw error if none
$DacAssembly = "$DacPath\bin\Microsoft.SqlServer.Dac.dll"

We COULD continue to just update it as Azure updates the SQL instance, but I feel there's probably a more sane way to check the export version of the bacpac. This would allow us to automatically roll forward our version check as it increases.

Failed, my google-fu has...


texts: A date column in a table won't save the value after a correct date was entered

texts:
I have a table with a column named Shipped Date. It is declared as a column of type DATE and retains NULL marks by default.

When I "edited" other columns in the table it worked, but when I entered in a standard date value such as '9/14/2017' into the Shipped Date column, the DBMS will let me enter it but it won't save it after I press TAB or ENTER.

I edited Shipped Date directly in the table. I also tried to enter "2017-14-09" and "September 09, 2017" into the column, but it still won't save it.

I put in the date with a format of "07-APR-9"8, and then I click the Commit button, and an error message popped up that said:

  Could not save file table ORDERS.

Tools/Preferences/Database/NLS/Date Format has DD-MON-RR.

Is RR supposed to be year? If I wanted it in month-Date-Year then I suppose that it will be MON-DD-RR. Is that correct? I did put it in the format of DD-MON-RR (15-09-17) but it still does not work.

I am using Oracle SQL Developer version 17.2.0.188.


texts:
I have a database having Millions of records in three tables. Now i want to split these three tables in multiple tables to reduce redundancy and data duplication.

A lot of records(Persons) have multiple city, address , telephone, fax.

So New tables will be Address, ,City ,Postcode ,State ,Country ,Telephone ,Fax

My question is , Is it fine to have City, Postcode, State, Country, Telephone tables separately or Just merge these tables in address table?


texts: change optimizer's plan on using indexes

texts:
When I run a query as follows, the run time is less than 1 sec.

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
    FROM source_price_curve spc
    WHERE spc.as_of_date >= @as_of_date_from

It utilizes the non clustered index based on maturity date column in the table.
I then added a temp table in join as follows

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
FROM source_price_curve spc
INNER JOIN #source_price_curve_list spcl
    ON spc.source_curve_def_id  = spcl.price_curve_id
WHERE spc.as_of_date >= @as_of_date_from

In this case also same index is seen utilized(unless recompiled or forced to use new index by hint),however, performance was decreased dramatically(to 12 secs),which was solved by using cross apply condition(as suggested by Paul).

My concern is, is there any ways to change optimizer's plan or let the optimizer  automatically choose a new index as used in join or filter condition by new query ?
(As far as I know, the optimizer uses the same index unless recompiled or forced to use a new index as a hint.)

Thanks in advance.


texts: Setting up AG Groups with Current Failover Cluster

texts:
Environment

Windows: 2012 R2 Standard
SQL: SQL 2014 Enterprise Edition
Setup: Two Node Windows Failover cluster with SAN

Changes:

We want to introduce Availability Group to the instance by adding a second SQL instance running at a DR site. It will be Virtual server with attached drive. For Clarity, we will call SQL instance running on two node cluster with SAN as S1 and second SQL instance at DR site as S2

Questions:

Can we create AG groups after Adding S2 to S1 cluster? In the event of S1 failure and it failsover to Node 2, would AGs failover to S2 instance?
Start fresh by creating two instances on new servers setup with AG and then migrate DBs to them?
Licensing - Do we have to pay licensing for both Primary and Secondary instance even though everything will be running on Primary and will failover in the event if Primary goes downs?

Thanks!


texts: Performing query based on another query's result

texts:
First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single query.

select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid,
category, brand, [Sales Price], Size, Format, manufacturer
from prdcnen where pagedetailid = '67197228466'

select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid,
category, brand,[Sales Price], Size, Format, manufacturer
from prdcnen where Adblockid = 1521368536511158

How can I merge these two statements?


texts: Peak load upgrade and downgrade SQL Server 2017 Standard  SQL Server 2017 Enterprise

texts:
We are currently running on SQL Server 2017 standard edition and for higher traffic weekends like Mothers day we want to go on SQL Server 2017 enterprise version and during non peak hours we want to come back on Standard. We run on AWS EC2 images of respective versions. My question is

Can we just take the backup of 2017 standard and restore on 2017 enterprise? I mean do we have to do an in place upgrade and not a backup and restore
Similarly can we do a restore from 2017 enterprise to 2017 standard during non peak hours? I mean it is mandatory to do an in place downgrade

We tried the backup restore option and running into memory issues
What is the best approach?


texts: Environment variable for two oracle_home

texts:
i have installed oracle binaries at two different location on Oracle Linux , /u01 & /u02
when i set environment variable for databases created for oracle_home in /u01 it works fine as i edited .profile but when i set environment variable for the databases created in /u02 it does not change the environment and i use to set it manually . Like , PATH, LIBRARY, CLASS PATH, ORACLE_BASE etc.

i have tried setting both the environment variable in .profile but it seems to be not working.

where do i make the entry to change it automatically ?


texts: Combine two event tables into a single timeline

texts:
Given two tables:

CREATE TABLE foo (ts timestamp, foo text);
CREATE TABLE bar (ts timestamp, bar text);

I wish to write a query that returns values for ts, foo, and bar that represents a unified view of the most recent values. In other words, if foo contained:

ts | foo
--------
1  | A
7  | B

and bar contained:

ts | bar
--------
3  | C
5  | D
9  | E

I want a query that returns:

ts | foo | bar
--------------
1  | A   | null
3  | A   | C
5  | A   | D
7  | B   | D
9  | B   | E

If both tables have an event at the same time, the order does not matter.

I have been able to create the structure needed using union all and dummy values:

SELECT ts, foo, null as bar FROM foo
UNION ALL SELECT ts, null as foo, bar FROM bar

which will give me a linear timeline of new values, but I'm not quite able to work out how to populate the null values based on the previous rows. I've tried the lag window function, but AFAICT it will only look at the previous row, not recursively backward. I've looked at recursive CTEs, but I'm not quite sure how to set up the start and termination conditions.


texts: Can't use SQL Server database on a networkpath

texts:
I am working on a project for a very long time now. It's a .NET application which works with a SQL Server database (.mdf, LINQ to SQL). Now I am almost finished and wanted to run some tests.

But here is where I totally screw up. Until now I had my application only running on my own pc with the .mdf file being stored locally. What I actually planned was an application which connects to a database stored on a networkpath from different machines.

Now I am reading that this is not possible with SQL Server databases?! And I also get the message:

  The file xxx.mdf is on a network path that is not supported for
  database files.

Can anybody think of an option how I could solve this problem without reprogramming the whole database stuff again?

One possible a clearly desperate solution I thought of first, was to:

Let my application find the database file on the network path
Copy the .mdf + .log files to a local folder
Do the Read/Write operations...or whatever the user wants to do
And at the end copy/replace the whole .mdf + .log files back to the network folder

If there is no other solution for my problem, would my solution work? If it would, what would be a possibility to avoid concurrent updates?

Please help and thanks in advance!


texts: Unexpected result when joining a table in a year range

texts:
I'm working with an users, user_roles and point_standards. Users need to achieve a certain points in a certain period based on which role they have. Points are retrieved from courses which are done in a specific year. Retrieving the user points is not the problem.

For the certain points a user has to achieve, a criteria is defined. These are made for 3 years and can not overlap. See the example below.

This means, from 2018 to 2020, the user with the role that is assigned to these criterias needs to achieve 93 points.

In 2018, the user should achieve 31 points (93 / 3).

The problem is when the user wants to check the criteria for years corresponding to more criteria. Lets say the user wants to check the criteria in the years 2018, 2017 and 2016. This means:

(93 / 3) + (50 / 3) + (50 / 3)

When ceiling these sums, this should result into a criteria of 65 points for 2018, 2017 and 2016.

For my expected end result, I want to get an overview with all users and the criterias for specific years.

In the example below, I'm trying to retrieve the criteria for a single user for the years 2018, 2017 and 2016.

SELECT `users`.first_name,
        ps.points

FROM `users`
       # Join the user roles table
       INNER JOIN `user_roles`
         ON `users`.`role_id` = `user_roles`.`id`

       # Get the criteria for 2018, 2017 and 2016
       LEFT JOIN (SELECT id, role_id, COALESCE(points / 3) AS points
                  FROM point_standards
                  WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)) ps
         ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC

This results in 2 rows with the correct criteria.

Expected result

In this case, it should return 3 rows. This because of I want to see the criteria of the years 2018, 2017 and 2016.

I then hoped I could sum the points of these 3 rows for each user.

Questions

Why is it only returning 2 rows instead of 3? I've selecte 3 years to look in.
How can I sum these amount of rows together so I can get an overview of all users?


texts: Log Shipping and Ola's Hallengren's Scripts - Can they work in unison?

texts:
I have a single instance database server with several databases.  Some do not require log shipping and some do require log shipping.

Is there a way to edit Ola's LOG backup job to ignore the databases I have configured for log shipping and backup all the remaining log files?

The only way I can think to do this without turning up a new server or create a secondary instance is not using Ola's script and create my own custom maintenance plans.


texts: Biggest possible double/float for a column in my MySQL database

texts:
I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking to store only up to two decimal points, and will only handle operations with numbers that have 2 decimals exclusively.

I have chosen my column type of double and unsigned, but whenever I store a row with a value bigger than one million, it gets truncated to 999999.99

Why is this? I am looking for the right datatype/solution. If you could point me in the right direction, I will appreciate it a lot!

Thank you for your time in advance!

Cheers.


texts: Split databases into groups of roughly equal datasize

texts:
I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size.

I want a table that contains every database and these databases are in 7 different Groups (Days of weeks - monday thru sunday).

Code that Groups into 7 Groups, but not equal size:

SELECT
    db.name,
    NTILE(7) OVER(ORDER BY DbSize.TotalDataSize) as bucket, --how to order?
    DbSize.TotalDataSize * 8 / 1024 as size_mb
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize

How can I order so not all big databases end up in the same bucket?

Needs to support SQL Server 2008 and newer.

Edit:
it doesn't matter if, let's say, 1 database is huge and others very tiny. The big database would then (optimally) reside in its own Group, but could still be larger than any other. The important thing is that not the 2-3 biggest databases are in the same Group.

I could do a cursor and for each database, see which Group has the least amount of data in it, but I felt it must be some better way to achieve this using set-based code?


texts:
We want to track progress of some projects, and I thought I'd make a mysql table of the checklist items that we'll be tracking completion dates and correlated data for.  This would be an ordered list, but being likely to be updated periodically, inserting a value in the middle is likely, and would be a nuisance.  I was wondering if there's a way of doing an insert along the lines of alter table, i.e. "insert into table set blah=blah after id=4", where id is an auto_increment and everthing 4+ would get renumbered.  Perhaps something like this post on renumbering: https://stackoverflow.com/questions/2643371/how-to-renumber-primary-index

I guess you could do this variation on that post:

SET @i=5
UPDATE table_name SET id=(@i:=@i+1) where id >= 4;
INSERT into table_name SET id=4, blah='blah'

That doesn't seem too complicated, though doing it this way you probably wouldn't want it to be auto_increment (or at least it doesn't buy you much beyond the initial setup).  I'm open to a better idea though...


texts: Storing data files in separate physical drives

texts:
Does storing sql server data files for 1 database in different drives gain performance? Is it recommended to do so? Any benefits gained from splitting the data files to multiple physical drives? There is no partition tables .The db is oltp which is read and write intensive.

Example database
AdventureWorks

D:\Data\AdventureWorks_1.mdf
D:\Data\AdventureWorks_1.ndf
E:\Data\AdventureWorks_2.ndf
F:\Data\AdventureWorks_3.ndf
F:\Data\AdventureWorks_4.ndf


texts: Bring back Master database from Single_user to Multi_user mode

texts:
Over the weekend, one of our colleagues applied the latest patch SQL 2012 SP3 CU8. But when we came in this morning 'master' database is in Single_User mode.

I did tried the steps mentioned in this blog https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e27bf0e-97df-4d2b-aa9d-e887b5b61504/master-database-in-single-usermode?forum=sqldatabaseengine by backing up the master database and restore it with different name, then changing to Multi_user mode and finally changing the file names, before restarting the service.

But it didn't work for me.

Also, checked the startup parameters and it doesn't have the -m parameter (single instance mode)

Any ideas?

Notes: Here is the error log snippet..


texts: Problem Getting Simple Replication to Work in SQL Server 2008/2012

texts:
I can't resolve a problem with replication. The publisher/distribution side seems just fine, but when I subscribe and view in the replication monitor I always get this error: "a required privilege is not held by the client"

I thought I had it figured out with an MSDN article explaining the SQL Agent user/pw needs to be set in SQL Config Mgr (instead of Windows Services) -- that was peculiar, but it did describe our situation. Then, we reset the user/pw using SQL Config Manager and I still get the same error.

I deleted my subscription, and setup a new one, but still the same error.

FWIW the publisher/distributor is SQL Server 2008 and the subscriber is 2012 (doing pull subscriptions).


texts:
I am trying to run the Select query(linked server) from SSMS to get the data from PostgreSQL on AWS cloud. My query is running fine in SSMS but as soon as I enter the following line of code

and c.created_date >=concat(to_char(CURRENT_DATE - interval '7 day', 'yyyy-mm-dd'),' 00:00:00'):: timestamp

and c.created_date <= concat(to_char(CURRENT_DATE - interval '1 day','yyyy-mm-dd') ,' 23:59:59') ::timestamp

it starts giving me ERROR: date/time field value out of range: "2020-06-0700:00:00";

created_date field in my PostgreSQL is timestamp without timezone

Which datatype should I chose which is compatible with SQL Server?


texts: Fastest Way to Move Data from Current Database for Multiple Tables to New Database?

texts:
This is my first question and post so let me know if I can improve.  I'll err on the side of providing too much information.

I'm a fairly new DBA at my company, less than two years with the DBA team, and have been tasked with coming up with a way to move data from an existing database for one specific entity to a new database which does not yet exist.  The database is on an instance running SQL Server 2012 SP1 Standard edition.

We currently have a custom PowerShell cmdlet called purge-entitydata.  This cmdlet will perform a delete in all 160 user tables in the current database based upon the entity value.

Example

purge-entitydata -entity <Entity>

However, before we delete all of the entity's data in the current database, we need to move only that entity's data to a new temporary database called [Entity_Backup] in case a downstream process fails.

Here are some caveats:

The 160 user tables may change several times a year.  Some may be deleted or new ones may be added.  I was thinking of going the SSIS route but didn't want to have to update the package with a new data flow task everytime a new table is added or removed.
Just the data and the tables themselves need to be moved.  I won't need to move views, stored procedures, or indexes from the current database to temporary database.
The current database and user tables contain data for over a thousand entities.  I just need to move the data for one entity over to the temporary database.
If the downstream process succeeds, then the temporary database will get dropped.
The database is large, over 200 GB.  Also, this server is the publisher for a pull merge replication subscription and is also the publisher for two transaction replication subscriptions. I'm thinking that taking a backup would slow replication too much.

I was thinking of using sp_msforeachtable 'SELECT * INTO [Entity_Backup].? FROM ? WHERE Entity = $Entity' and Invoke-Sqlcmd but am worried this would take too long since it does not run in parallel.  An entity has approximately three to five million rows.

What be the most efficient/fastest way to move data from the 160 user tables to a new database which can be done from inside the PowerShell cmdlet? Does anyone have any suggestions which may be faster than ms_foreachtable?


texts: Database which was set up for logshipping is dropped

texts:
I dropped an old unused database. Then I found it was set up to be the source database of a logshipping setups. And there are a lot failing jobs in our job running status report now.
How to remove the logshipping settings after the database is already removed?


texts: Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

texts:
I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB.

I know the risks involved based on resources such as http://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html  and also https://serverfault.com/questions/262936/is-replication-from-mysql-5-5-master-to-a-5-1-slave-possible

However, this is a very special case because I am trying to replace the MySQL 5.1 DB with a 5.6 version, and the only way to have minimum downtime is to have a 5.6 DB set up first and then let it replicate from the 5.1 DB, which will in turn also replicate from the 5.6 DB to form a cyclic replication. The purpose of this is to ensure that during the switchover (when the 5.6 DB will be the active one receiving the live writes instead of the 5.1 ), no write data would be lost.

I was curious if anyone has ever had this kind of setup before with the 5.1 DB replicating from the 5.6 DB, and if there were any issues you encountered. I am not sure what SQL commands in the 5.6 DB could be problematic for the 5.1 DB during replication.

To be exact, the 5.6 version is 5.6.21 and the 5.1 version is 5.1.73

Thanks


texts: Connection to MS Access from Oracle over a DB Link

texts:
I need to establish a connection between a MS Access database, which is placed on a network share on a windows box, and an Oracle database on a Unix Box. I need to query the Access tables from the Oracle database. Steps I have followed:

Created a System DSN which points to the MS Access database file (mdb) on the network share. Created an file in the /hs/admin path of the Oracle Gateways directory with the entry:

HS_FDS_CONNECT_INFO = DSN
HS_FDS_TRACE_LEVEL = off

I created a listener which refers to the above DSN.

On the Unix box, created a tns entry and a public database link.

Upon querying a table over the db link, the error is:

  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path.   >Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. {HY024,NativeErr = -1023}
  ORA-02063: preceding 2 lines from DSN

However, when I create the System DSN which points to a file on a local drive on the windows box, I am able to query tables over the db link.

Please advise on where I need to make changes.


texts:
I have a query that looks like this:

SELECT
B.Company,
COUNT(*) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC

However, there is a field name "Ticket" in table1 that has duplicate values, that I need to exclude. How can I return the count with excluding duplicate "ticket" values.


texts: Can SQL Server start taking backups by itself?

texts:
We have a SQL Server which started to take backups by itself every now and then. It started for a few days ago.

When it fails an error is logged saying that source is "Microsoft SQL Server Native Client 10.0"
It also says:

  "SQLSTATE: 42000, Native Error: 3201 Error state: 7, Severity: 16
  Error message: Cannot open backup device
  '{94C30FE5-1C62-45C7-8E28-4F67BCF89210}682'."

How can this happen? Can I find out what part of the code which does this?

It is not a part of any jobs or similar, the time for the backup is different from day to day, it does not try to take a backup every day but most of them. If it fails it retries a lot of times and after a veil its stops.

Edit:
The first clue to find out why these backups were taken I found from Kevin3NF advise, were I could confirm that it was the SQL Server VSS Writer which were taken the backups.
Then John Eisbrener commented that if it is VSS writer it is probably when one takes a backup of the entire OS. I then found that someone has had a similar problem: Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration?
We have now confirmed that it happens when our new 3rd party tool is taking a backup of the entire OS.


texts:
I am having difficulty in using a variable name for the database. The below script searches through all databases and finds database that match a certain condition. The matching databases are added to table #t.
I then loop through each database by setting the @DB1 to each database in turn. The problem I'm getting is when it comes to using the database name in @DB1. Any idea how I can get each of my matching databases to update?

create table #t (
    ID int IDENTITY,DBName sysname not null
)
go
EXEC sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.SDE_server_config'') is not null insert into #t (DBName) select ''?'''

Declare @RowCount int
Declare @Counter int
Declare @DB1 varchar(max)

set @RowCount = (Select Count(*) from #t)
set @Counter=0

While @Counter <= @RowCount
Begin
select @Counter = @Counter + 1

set @DB1 =  = (select DBName FROM #t WHERE ID = @COUNTER)

:SETVAR DatabaseName @DB1

USE $(DatabaseName);

UPDATE dbo.SDE_server_config
        SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;

End


texts: mysqldump select queries appearing in slow query log after upgrade

texts:
We recently upgraded from MariaDB 5.5 to MariaDB 10.5
Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump.
One of the tables is an InnoDB table with 125,000 records.  The other is a MyISAM table with 220,000 records.
I guess this might not seem like any great concern, however for many years we never had anything in the slow query log related to mysqldump.  So I am wondering if we should be worried?  Did the behaviour of mysqldump change between versions 5.5 and 10.5, or could there be some other local setting that we perhaps had optimsied previously, that now isn't?
Our slow query time is set to 5 seconds, which is what it was previously.  The offending queries are taking just under 6 seconds.
Thanks
EDIT
After further investigation, it turns out we are piping mysqldump through gzip, to compress the resulting dump files.  Removing gzip from the equation results in each dump completing in 3 seconds or less.
The fact still remains that our setup remained unchanged other than the upgrade from MariaDB 5 to 10, and this didn't seem to be an issue previously.
In any case, whilst piping to gzip results in a nice "one liner", I have changed it to dump the files as plain text, then gzip them afterwards. I feel better knowing that the dump queries complete much faster, and aren't held up by gzip.


texts: How can I enable distributed transactions for a linked server?

texts:
I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver.

I'm trying to enable distributed transactions. I believe this provider does support them as per IBM documentation (unless I'm misunderstanding that link).

I'm running the following query:

BEGIN TRANSACTION
    UPDATE LINKEDSERVER.LINKEDDATABASE.SCHEMANAME.TABLENAME SET COLUMN = VALUE

    SELECT CAST('qwerty' as integer) -- we want an error here so the transaction fails
COMMIT TRANSACTION

I get the following error:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2".
  Msg 7391, Level 16, State 2, Line 2
  The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" was unable to begin a distributed transaction.

All the research I've done on this error has met with people wanting to disable distributed transactions, not get them working. I've already set "Enable Promotion of Distributed Transactions" to true on the linked server properties.

Any ideas how I can get this working?

EDIT: More information/update. I was about ready to give up on this for now and wait for help, when I noticed this in the windows logs:

  A caller has attempted to register an XA resource while XA transactions are disabled.

I found this link which told me how to modify the settings for the MSDTC.
After playing with the settings and restarting my server things started to work (kind of):
(specifically, I enabled network DTC access, allow inbound/outbound/enable XA transactions).

At present, the query runs and commits/rolls back as expected, but seems to work some of the time, but error other times. The error is:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Internal driver error."

EDIT2: Now it's not working at all again. Error message:

  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "ODBC driver could not start new transaction.".
  OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[Microsoft][ODBC Driver Manager] Illegal operation while in a transaction server component transaction".


texts: Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

texts:
I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a folder on the SQL Server server.
The problem is when I created a job in SQL Server Agent and tried to run the job.  That is when I get the error:

Executing the query "EXEC ? = dbo.usp_Clear_All_JDAWG_Tables" failed with the following error:

"The EXECUTE permission was denied on the object 'usp_Clear_All_JDAWG_Tables' database 'JDAWGD' schema 'dbo'.".

Possible failure reasons:
Problems with the query
"ResultSet" property not set correctly
parameters not set correctly
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

The usp_Clear_All_JDAWG_Tables is the name of the first of many packages this job is trying to run.  The stored procs themselves do not use parameters but in the SQL Task I have commands like this to run the stored proc:
EXEC ? = dbo.usp_Load_JDAWG_Tables_From_Persons_All_Agencies

Is there a better/different way to run a stored procedure in the SQL Task?
As a side note, I was able to create and successfully run an Agent job that directly executed the stored procedures that the packages run.


texts: How can I downgrade an AWS Aurora MySQL 5.7 2.09.2 to an earlier 5.7 version?

texts:
During a test, I applied a branch of our Terraform description that upgraded a couple Auroras from 5.7 2.04.x to 5.7 2.09.2 without losing the data (as expected). When attempting to apply the main branch, however, we realized Auroras are not easy to downgrade.
The solution (we were OK with losing the data) we used was to destroy the Aurora clusters manually, but that'd require a back-up and restore on a real cluster with data we wanted to preserve, assuming it's possible to do it across versions (which I'm no longer doing).
Since one of the key selling points of Aurora is the detachment of data storage and compute nodes, I was a bit surprised there is no easy way to revert an upgrade. Going to the console to modify the cluster manually no longer shows previous releases.
Is there a way to roll back changes without requiring to restore the data?


texts: CentOS7 Virtual Machine postgres10 not initializing

texts:
I had postgres9.6 installed and working the other day but the version wasn't compatible.

I used YUM REMOVE to remove that one and then followed the instructions here
https://www.postgresql.org/download/linux/redhat/
to install postgres10.

However anytime I do the

/usr/pgsql-10/bin/postgresql-10-setup initdb

it returns

Initializing database ... mkdir: cannot create directory ‘/var/lib/pgsql’: Permission denied

Initializing database ... failed, see /var/lib/pgsql/10/initdb.log

However the log file is actually empty.

I've tried a number of things suggested with no success. Any advice would be great as to where to start looking about this


texts: How can I fetch parent table's value within a trigger?

texts:
I have a PARENT table and a CHILD table.

PARENT
------
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL

CHILD
-----
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL
PARENT_ID BIGINT NOT NULL

I want to create a trigger which does

when a CHILD row inserted or updated
check the NEW.NAME
and if the NEW.NAME is NULL or EMPTY
use PARENT's NAME as NEW.NAME

How can I do that?


texts: What does the output of a JOIN statement look like?

texts:
I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.

Let's say I have 2 tables:

CREATE TABLE Cities (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    city tinyblob
);

CREATE TABLE Users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username TINYBLOB,
    city INT UNSIGNED,
    FOREIGN KEY (city) REFERENCES Cities (id)
);

If my application is to run an SQL query to get a user's profile data, how would I use a join to get the city associated with a user's record, and how would the outputted record appear?


texts: Find and print the restaurant name and city for all restaurants serving Salad

texts:
I'm new to SQL and having some trouble with this one, but I feel like the answer is fairly obvious and I'm missing something. Here is the query I've tried:

SELECT r.restaurantName, r.city
FROM Restaurant r, MenuItem m
WHERE m.itemNo = 44;

These are the tables I'm using:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);

Inserted data:

INSERT INTO Dish Values(13, 'Spring Rolls', 'ap');
INSERT INTO Dish Values(15, 'Pad Thai', 'en');
INSERT INTO Dish Values(16, 'Pot Stickers', 'ap');
INSERT INTO Dish Values(22, 'Masaman Curry', 'en');
INSERT INTO Dish Values(10, 'Custard', 'ds');
INSERT INTO Dish Values(12, 'Garlic Bread', 'ap');
INSERT INTO Dish Values(44, 'Salad', 'ap');
INSERT INTO Dish Values(07, 'Cheese Pizza', 'en');
INSERT INTO Dish Values(19, 'Pepperoni Pizza', 'en');
INSERT INTO Dish Values(77, 'Veggie Supreme Pizza', 'en');

INSERT INTO MenuItem Values(0, 0, 13, 8.00);
INSERT INTO MenuItem Values(1, 0, 16, 9.00);
INSERT INTO MenuItem Values(2, 0, 44, 10.00);
INSERT INTO MenuItem Values(3, 0, 15, 19.00);
INSERT INTO MenuItem Values(4, 0, 22, 19.00);
INSERT INTO MenuItem Values(5, 3, 44, 6.25);
INSERT INTO MenuItem Values(6, 3, 12, 5.50);
INSERT INTO MenuItem Values(7, 3, 07, 12.50);
INSERT INTO MenuItem Values(8, 3, 19, 13.50);
INSERT INTO MenuItem Values(9, 5, 13, 6.00);
INSERT INTO MenuItem Values(10, 5, 15, 15.00);
INSERT INTO MenuItem Values(11, 5, 22, 14.00);

Example of one of the joins I have tried:

SELECT DISTINCT Restaurant.restaurantName, Restaurant.city
FROM Restaurant
INNER JOIN MenuItem
ON MenuItem.restaurantNo = Restaurant.restaurantID AND MenuItem.itemNo = 44;


texts: MongoDB: The collections metadata lock is taken

texts:
Running a mongodb 2.6 cluster on localhost with four shards and four replication sets.

Using a for loop with 100 iterations that pre-splits chunks among the existing shards. I am getting the error The collections metadata lock is taken after a couple of minutes

mongos> for( var i=0; i < 100; i++) { sh.splitAt("test_cluster.presplit_demo", { x:1000000 * i / 100 } ); }
{
    "cause" : {
        "who" : {
            "_id" : "test_cluster.presplit_demo",
            "state" : 2,
            "who" : "localhost.localdomain:27000:1402761131:1123537020:conn19:1622303703",
            "process" : "localhost.localdomain:27000:1402761131:1123537020",
            mongos&gt; for( var i=0; i &lt; 100; i++) { sh.splitAt("test_cluster.presplit_demo", { x:1000000 * i / 100 } ); }
{
    "cause" : {
        "who" : {
            "_id" : "test_cluster.presplit_demo",
            "state" : 2,
            "who" : "localhost.localdomain:27000:1402761131:1123537020:conn19:1622303703",
            "process" : "localhost.localdomain:27000:1402761131:1123537020",
            "when" : ISODate("2014-06-14T15:56:16.439Z"),
            "why" : "migrate-{ x: 360000.0 }",
            "ts" : ObjectId("539c70a07b296ac1c721eca4")
        },
        "ok" : 0,
        "errmsg" : "the collection's metadata lock is taken"
    },
    "ok" : 0,
    "errmsg" : "split failed"
}

I see the lock in config: db.locks.find(), removed the lock but it just comes back. If I reduce the number of iterations to 50, the for loop completes successfully. There is more than enough disk space.

Question

Anyone else run into this issue with pre-splitting and what did you do to resolve it?


texts:
In SQL Server 2014, I have the following xml fragment :

<salesorder>
   <ordnum>123</ordnum>
   <orddate>2017-04-17</orddate>
   <details>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>5</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H1</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>1.50</value>
            </specification>
         </specifications>
      </detail>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>10</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H2</value>
            </specification>
            <specification>
               <name>Colour</name>
               <value>Red</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>2.75</value>
            </specification>
         </specifications>
      </detail>
   </details>
</salesorder>

I would like to extract the following data :

ordnum   itemtype     qty   price
123      Stationery   5     1.50
123      Stationery   10    2.75

Is this possible to accomplish in T-SQL?

Thanks in advance,
MP


texts: Finding out who changed the owner of a database when performing a restore

texts:
A coworker who worked on restoring a backup changed the database owner from his name to 'sa' (rightly so) last night. While I had seen the his name in a SQL Server Management Studio window, I did not take a screenshot. He is now claiming that he did not restore that database.

I am an admin on the server and sysadmin on the database server. Is there a way to check who changed the owner of the database?


texts: SQLite vs MemSQL vs other SQLAlchemy compatible in memory databses

texts:
What is the recommended in-memory database which connects to SQLAlchemy?

Requirements:

Connects to SQLAlchemy.
Minimal memory usage when empty (it will store <50MB).
Easy to work with (e.g. Kafka vs RabbigMQ: Kafka requires much more user input/setup).
Reliability in terms of the database going down unexpectedly.
Can be accessed by multiple read applications.
Shareable across a local network.

The two databases I've researched are SQLite and MemSQL.
Some points so far:

MemSQL requires 8GB of memory, although I am unsure how much it uses when empty?
MemSQL has replication however only in the paid version and not the free community version.

Input from users who have used these solutions or others is very valuable. Which solution would you recommend?


texts: how to generate a sequence of values with differents formats in PostgreSQL

texts:
I want to find rows missing from a sequence of values in my table with differents formats.
For an example, look at this.
I want to find the missing lines via c_order_id and documentno. the one with '_p' at the end, and the other without '_p', just the numbers.
CREATE TABLE documents (
  "order_id" VARCHAR(22),
  "documentno" VARCHAR(20)
);
INSERT INTO documents
  ("order_id", "documentno")
VALUES
  ('100001120', 'PROFS/2021/02050_P'),
  ('100001125' ,'PROFS/2021/02055_p'),
  ('100001127', 'PROFS/2021/02056'),
  ('100001135' ,'PROFS/2021/02060'),
  ('100001139' ,'AGB/2021/02040'),
  ('100001172' ,'AGB/2021/02047')

Query used:
WITH cte as (
SELECT LEFT("documentno",12) lpart , MIN(RIGHT("documentno",5)) minpart, MaX(RIGHT("documentno",5)) maxpart  FROM documents
groUP BY LEFT("documentno",12))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER)  as missing_documentno FROM cte
except
select documentno from documents
ORDER bY missing_documentno;

This query generates
ERROR:  invalid input syntax for type integer: "055_p"


texts: What permissions are required to return rows from information_schema.schemata?

texts:
In postgres (I'm on 9.3.4) what permissions are required to return rows for

select * from information_schema.schemata

The 9.3 documentation says "are owned by a currently enabled role". Does that mean the user calling schemata must own (or be part of a role that owns) the various schemata?

Later in 9.5 documentation it says schemata that "the current user has access to (by way of being the owner or having some privilege)".

The account currently trying to run the query and getting 0 rows back has had grant usage on schema applied to it.

What are the privileges that allow a database account to see a schema in the information_schema.schemata` view?


texts: SSLServerHasCertificateAuthority is false but the certificate is signed by a trusted CA

texts:
I am enabling TLS/SSL encryption on a remote MongoDB instance (running on Windows Server 2012 R2). On the instance's server, devserver.external, I have created an SSL certificate. The CN on the subject is devserver.external and the issuer is company-issuer. On my computer (the one with which I am connecting to the remote mongo instance, Windows 10) I have company-issuer in my Intermediate Certification Authorities (when viewed through the Windows certificate manager). This is an internal company operated CA that is trusted on all company machines, including the dev server and my machine.

When connecting to mongo, via either mongo on the command line or the Robo3t client, the connection succeeds over SSL but db.serverStatus().security.SSLServerHasCertificateAuthority is false. From the mongo docs:

A boolean that is true when the TLS/SSL certificate specified by net.ssl.PEMKeyPassword is associated with a certificate authority. **false when the TLS/SSL certificate is self-signed.**

So it would seem mongo is interpreting my CA-signed certificate as a self-signed certificate. This is obviously unwanted behaviour.

My mongod.cfg is:

systemLog:
    destination: file
    path: E:\MongoData\Log\mongod.log
storage:
    dbPath: e:\MongoData\DB
setParameter:
    enableLocalhostAuthBypass: false
security:
    authorization: enabled
net:
    ssl:
        mode: requireSSL
        PEMKeyFile: C:\Program Files\MongoDB\Server\3.4\mongodb2.pem

My mongodb2.pem is:

-----BEGIN PRIVATE KEY-----
<base64 encoded private key>
-----END PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
<base64 encoded certificate>
-----END CERTIFICATE-----

I've not included a CA file because as stated in the configuration reference:

Changed in version 3.4: If --sslCAFile is not specified when connecting to an TLS/SSL-enabled server, the system-wide CA certificate store will be used.

My interpretation of this is: As I have not included a net.ssl.CAFile in my mongod.cfg, my mongo server will look to the Windows certificate store for a list of trusted CAs. In which it would find my company-issuer CA, which is the issuer for the certificate in mongodb2.pem.


texts:
Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance.

I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference of

FULLTEXT(title,body)

and

FULLTEXT(title),
FULLTEXT(body

Is there any effect on the search performance and the ranking of results.


texts: How to tell if Database Certificate Used?

texts:
I plan to migrate a SQL Server database to another server. There is a certificate listed under the Security > Certificates view in SSMS Object Explorer.

I ran the below query to check if database is encrypted and it is not...

select name, is_encrypted from sys.databases

How can I find if this certificate is used? And how do I ensure successful migration?

Thanks!


texts: "Can't find file mysql/plugin.frm" when starting up MySQL

texts:
When I start my MySQL server, I get this error:

/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

I have run mysql_upgrade to rebuild it, and it works to rebuild. Before I did that, I removed and installed MySQL. However, when I tried to re-start, I get the same error. I am sure it is not a permission problem, as all files and directories are created by the mysql user.

Could you advise me on how to fix this error?


texts:
On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am.

Every Monday the first transactional backup is 7 Gb. I want to know why, my database is only 500Mb.

Am I correct in thinking that something is running between 6am and 8am that is causing this massive tran backup? Or could it be something over the weekend that causes the first tran backup to be so large.


texts: When PostgreSQL query <Value returns =Values

texts:
I'm running a query using PostgreSQL 9.6.6 and when querying:

SELECT *
FROM table
WHERE
      threshold_value < VALUE

returns all values = VALUE and not < VALUE as it should. In the same way if I query

SELECT *
FROM table
WHERE
      threshold_value = VALUE

Shows no values at all when all threshold_value (values) are equal to VALUE (VALUE is 1.4)

My threshold_value column is of type real. Should this be happening, or is it a known bug? Is this related to conversion errors between the python implementation of PGAdmin and the DB, or am I missing something?


texts: Index on Primary Key: is there any reason for that?

texts:
I'm auditing the schema of a MySQL database used by a off-the-shelf e-commerce application and I found this as output of SHOW CREATE TABLE thistable:

CREATE TABLE `thistable`
(
    `case_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `company_id` int(11) NOT NULL DEFAULT '0',
    (...)
    `timestamp` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`case_id`),
    KEY `idx_case_id` (`case_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21688281 DEFAULT CHARSET=utf8

It explicitly adds an index on a primary key.

Is this a blunder of the database designer, or is there actually a reason why is that?

It is worth noting that this table was MyISAM in origin.


texts: Overcome Runtime error when exporting large data table from PostgreSQL

texts:
I am very new to using PostgreSQL. I have created a query that compiles a table with more than 100,000 rows and 20 columns of data. The query works and loads the table fine in the PostgreSQL preview window. However, when I click to export the table to CSV in PostgreSQL I get a Runtime application error that says PostgreSQL must automatically close (and I lose everything). I do not get this error when I query and CSV export much smaller tables.

I would appreciate any advice on how to overcome this Runtime application error for this large table.

I do not want to divide my large table into many small tables since this will take too much time and creates rooms for merging multiple tables errors.

Thank you for any help you can provide!

CODE:

SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5'
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder


texts: Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf

texts:
I've got the error related to max_allowed_packet:

  [S1000] Packet for query is too large (2,127 > 2,048). You can change
  this value on the server by setting the ‘max_allowed_packet’ variable.

So the limit is 2048. I've checked /etc/mysql/mysql.conf.d/mysqld.cnf and the value there is 16Mb:

max_allowed_packet = 16M

I've ran the query:

SHOW VARIABLES LIKE 'max_allowed_packet';

Which gave me 2,048. So the values are different. Is my assumption correct that the value in mysqld.cnf specifies limit for server while the query gave me the limit for client side? If so, where do I configure the value for the client side?

By running this query:

SET GLOBAL max_allowed_packet=16777216;

I only set the variable for the current session and as I understand it'll be reset after I restart the database. What's also interesting that:

SHOW VARIABLES LIKE 'max_allowed_packet';

still gives me 2,048m but:

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

gives me 16777216.

I need to set it in the client configuration file. But is there such a thing? I've done some reading but it's still vague.


texts: Why does MySQL Workbench hang forever on queries that run instantly via command line?

texts:
This query (and others like it) fail to run on MySQL Workbench but returns results nearly instantly when I use SSH to log into the server and then log into MySQL and paste the query in the terminal there.

What can I change about MySQL Workbench (or my queries or my tables) so that queries like this complete quickly instead of hang indefinitely?

I doubt I need to index my tables more (or differently) given that running the same query via command line works well.

Currently this one returns ~12k rows.

SELECT
    SUBSTRING_INDEX(x.fullNameFilled, ' ', 1) AS 'first_name',
    IF(INSTR(x.fullNameFilled, ' ') = 0,
        NULL,
        SUBSTRING(x.fullNameFilled FROM INSTR(x.fullNameFilled, ' ') + 1)) AS 'last_name',
        x.* FROM (
SELECT
    IF ((c2.fullName IS NULL OR c2.fullName=""), c2.emailAddress, c2.fullName) AS fullNameFilled,
    c2.*,
      `cf2`.`visitedCalendar`,
    `cf2`.`requestedCall`,
    `cf2`.`clarityCallTimeUtc`,
    `cf2`.`clarityCallTimeLocal`,
    `cf2`.`visitedApp`,
    `cf2`.`applied`,
    `cf2`.`receivedOffer`,
    gc.tag_labels
FROM
    contacts c2
        LEFT JOIN
    call_flows cf2 ON cf2.contact_id = c2.id
        LEFT JOIN
    (SELECT
        contact_tags.contact_id,
            GROUP_CONCAT(tags.label
                SEPARATOR ', ') AS tag_labels
    FROM
        tags
    LEFT JOIN contact_tags ON tags.id = contact_tags.tag_id
    GROUP BY contact_id) gc ON gc.contact_id = c2.id
WHERE
    c2.id NOT IN (SELECT DISTINCT
            c.id
        FROM
            tags t
                LEFT JOIN
            contact_tags ct ON ct.tag_id = t.id
                LEFT JOIN
            contacts c ON c.id = ct.contact_id
        WHERE
            (t.label LIKE 'unsub%')
                OR (t.slug IN (
                'deposit_paid',
                'current_client',
                'app_declined')))
        AND id NOT IN (SELECT DISTINCT
            c.id
        FROM
            contacts c
                LEFT JOIN
            call_flows cf ON cf.contact_id = c.id
                LEFT JOIN
            webcasts w ON w.contact_id = c.id
        WHERE
            (c.revenueCollectedAlready >= 7)
                OR (cf.appRating <= 1)
                OR (cf.attendance = 'flaked')
                OR (cf.receivedOffer <> 'No offer yet')
                OR (w.momentOfOfferUtc IS NOT NULL))

) x
 LIMIT 20000;


texts: Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a?

texts: I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value.  Is a two column primary key (A,B) the way to go even though my queries will only search on A?


texts: Denormalized tables within OLTP database

texts:
My company has a product centered around a fairly normalized mssql server database.  At this point we have several reports and a web application that needs the data in a much flatter fashion to display the data appropriately to the user.  Today we are using views/stored procedures to provide flattened data model needed by the reports and web app.

Currently there are performance problems though with several of the views due to the amount of aggregate data and joins being performed inside the views.  My thought was to instead provide denormalized tables that could be optimized for performance for both the reports and the web application.  These denormalized tables would be built at some interval of time or maintained as updates happened to the underlying oltp tables.

Is this a common thing to do?  Am I asking for trouble as far as performance just keeping these tables up to date?  What mechanisms are the best for building these sort of tables?  Is there a better way to do this than these denormalized tables?


texts:
I've been given an SQL Server to run maintenance plans on. After running a script called sp_blitz which checks the health and security status of the server and going through the errors logs I realized that there are up to 5 databases with instances of database corruption.

To have my own understanding an experience of these errors, I set up fresh maintenance plans for backup only just to monitor what errors I will get. The result showed that some databases were not being backed up at all and when I checked what the error was, for one of the databases, I got "Failed to flush the commit table to disk in  (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

I went on google to see what I could do and it said I could restore the database from a point where it was still ok. I checked the local disks for the most recent backup and that was 24/08/2015 which corresponded to what the database properties said in 'last database backup section'.

Can you please advice me on what you think my next steps should be sir or what I can do in a situation like this.

Also we are using SQL Server 2008 r2 sp1. Should I be looking to upgrade to sp3?


texts: Autorise computer accounts through AD group

texts:
I would like to grant SQL access for computer accounts.
It works if I declare a login for each account, like CREATE LOGIN [contoso.com\srv1$] FROM WINDOWS

but I can't make it works when using a AD security group containing the computername.
Say SRV1 is member of contoso.com\server_group , and I create a login for it like this CREATE LOGIN [contoso.com\server_group] FROM WINDOWS
. Now, trying to request the DB with the computer account the following error is raised :
Error get database Exception calling "Open" with "0" argument(s): "Login failed for user 'contoso.com\srv1$'."

Any idea ?


texts: Turn off listener logging in oracle 12c release 2

texts:
I am trying to turn off listener logging in oracle 12c R2 (SAP on oracle), but I am getting a TNS-12508 error. I have verified that the listener.ora is owned by the oracle user and I am performing the task as same oracle user, but I am still getting an error.

File Permissions

-rw-r--r--    1 oracle   oinstall       1151 Oct 23 13:48 listener.ora

Listener Error

LSNRCTL> set current_listener LISTENER_AAA.
Current Listener is LISTENER_AAA

LSNRCTL> set LOG_STATUS OFF
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

LSNRCTL> set log_status off
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=AAA.WORLD))
TNS-12508: TNS:listener could not resolve the COMMAND given

Am I missing something here?


texts:
I have the following query that is used to define a view in MySQL.  But the query takes forever to complete even if I perform the query outside the view.  I have a feeling that the inner select is what's slowing things down, but i have no idea how to optimize it further.  HELP PLEASE!

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    (SELECT
            COUNT(`tbl1`.`col1`)
        FROM
            `tbl1`
        WHERE
            ((`tbl1`.`col1` = `table1`.`col1`)
                AND (`tbl1`.`date` = `table1`.`date`))) AS `usage_count`
FROM
    `table1` `tbl1`


texts: Unique clustered index on indexed view fails because of aggregates

texts:
I want to create an indexed view similar to this (slightly simplified):

Create VIEW dbo._v1
WITH SCHEMABINDING
AS
    SELECT
         COUNT_BIG(*) as CB
        ,[ID]
        ,TITLE]
        ,dbo.getXmlTranslation1(acat.[TITLE], 'de-DE') as T
    FROM dbo.[ArticleCategory] acat
    Group by
         [ID]
        ,[TITLE]

Creation of a unique clustered index on this fails:

CREATE unique CLUSTERED INDEX __CLIX
ON dbo._v1([ID]);

  Cannot create the clustered index '__CLIX' on view 'db._v1T' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

I searched for a solution but can't find one. If I remove the GROUP BY (I don't really need here), I am told that the Unique Clustered index can't be created because of multiple [ID] results.
I added the scalar function call in order to workaround the XML operation within the indexed view that is not allowed as well.
I checked if the scalar funtion is deterministic, what is the case.


texts: In Microsoft SQL Server's linked server configuration, what does the "RPC" setting do?

texts:
I'm not talking about "RPC Out". That setting works as I expect it to: when "false", I can't call stored procedures on my linked server. When it's "true", I can.

The specific option I am referring to is in the GUI (SQL Server Management Studio) on the Linked Server Properties window for any linked server (under "Server Options" on the left). Or, you may also set this option by calling sp_serveroption @optname='rpc'.

"RPC" can be set to either true or false. However, I can't find solid documentation on what this actually means in practice. I can't find any situation in my experimentation that behaves differently depending on whether "RPC" is set to true or false.

Can anyone give an example of what behaves differently depending on this setting?

Thanks!


texts: What does "WSREP Aborter Idle" mean in a MariaDB Galera cluster?

texts:
I've tried to Google WSREP Aborter Idle but there are literally 3 results. Does anyone know what this means?

Should I be concerned that the these processes have been opened for so long or is this just part of the normal cluster operation?

Thanks!


texts:
When I try to RENAME/DELETE a specific UDF I get the following error:

  Lock request time out period exceeded (Microsoft SQL Server, Error:
  1222)

Note that if I try to ALTER it, the query runs indefinitely.

Here's what I did to try solving the problem without success:

DBCC OPENTRAN() returns "No active open transactions."
SELECT @@TRANCOUNT returns 0.
BlkBy column returned by sp_who2 is empty.
I restarted Windows server and manually stopped/restarted MSSQLServer service
chkdsk c: returns:

976657407 KB total disk space.
114105840 KB in 702392 files.
   424472 KB in 122799 indexes.
        0 KB in bad sectors.
  1885591 KB in use by the system.
    65536 KB occupied by the log file.
860241504 KB available on disk.

No problem with all other database objects. This is not a new UDF because I created it several months ago and it worked fine up to 1 hour ago.

If I run SELECT OBJECT_ID('dbo.f_GV'), then I cannot find returned ID value (1505492492) anywhere by executing select * from sys.dm_tran_locks. Is my database corrupted?


texts:
When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types.
Will this cause time out issues? Can anyone confirm whether this will cause time out / performance issues?


texts: Full-Text Index menu option not appearing in SQL Server Management Studio AND error when right clicking a table

texts:
I am using SQL Server Management Studio Version 11.0.3128.0 and SQL Server 2014 12.0.2361 Web Edition.

I have successfully created a Full Text Catalog following these instructions: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

When I get to the part to Create a Full Text Index, the GUI menu that appears like this:

Does not appear. All I get is this:

Why is that the case? I know I can create it in SQL:

create fulltext index on Production.ProductDescription(Description)
key index PK_ProductDescription_ProductDescriptionID`

But why won't the menu option show up? Full-Text search is enabled.

Note: I don't know if it is related to this error but when I right click on a table in this database, I get the following message (Even though I still get the second menu above after I close the error):


texts: Trouble with Ola Hallengren's @DatabasesInParallel parameter

texts:
I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I might be doing something wrong.

I have 4 DBs set up and I am manually running the following 4 commands:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AdminDB',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'Pop',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_StackOverflow2010',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_SUPERUSER',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

When I check the QueueDatabase table, I can see that the DatabaseOrder and RequestStartTime column are the exact same for all 4 databases. However, when you look at the column DatabaseStartTime it shows the first 2 databases starting at the same time and then the next 2 databases dont start until the 2nd database has finished doing its backup and verify check.

Is this the intended functionality with the parameters that I am using? I would expect all 4 databases to be started at the exact same time with this parameter.

EDIT 11/14: Updated title/wording of post

Original post said it was backing up only 2 databases at a time but it was really only doing 1 database at a time. The first DB that is backed up in my test is so small that I didnt realize it finishes almost immediately and the second on starts. However you can see this


texts: Does it make sense to put a unique constraint on a nullable soft-delete flag?

texts:
So we currently have the following:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

Now, there's the requirement to allow infinite deleted records, but to only allow a single non-deleted record for each date+company.

I suggested to change the schema to:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

While my coworker is of the opinion that that is "going too far with constraints" and that we should just rely on the uniqueness checking in the application.

Is there a generally accepted best practice here?


texts: GROUPING SETS returning unexpected results with calculated expression

texts:
Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:

SELECT RN10, RN10 / 10, COUNT(*) FROM
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0)
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

it's plan is here: first query plan

and

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99))
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

the corresponding plan is here: second query plan

Both the queries first calculate some expression for aggregation, RN10 / 10 in the first case and SUBSTRING(RN,3,99) in the second, then the same expression is used in the SELECT clause but as the first plan shows it's re-calculated in the first query and it's not in the second.

As the result we have NULLs in the first result set that is quite unexpectedly:

Can someone explain why the first query makes the calculation 2 times (one in aggregation and one more time in the final select) while the second makes it one time only?


texts: Invoke SQLCMD doesn't work when used with xp_cmdshell

texts:
I am trying to get  disk space report from bunch of servers and insert them into sql table..

below is sample of what i am trying to do,Server names will be populated from a table and passed as a comma seperated list.Even passing one server also has the same result

set @ps = 'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume '+@servernames+'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label

$insertquery="
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           (''$pscomputername'' ,
           ''$name'' ,
           ''$capacity'',
           ''$freespace'',
           ''$Label''
           )
GO
"

Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname

}

"';

Now i try to pass the variable to xp_cmdshell like below

execute xp_cmdshell @ps;

When invoked in SSMS,above returns null,but works in powershell..

Any ideas why ?

Below are a few things i tried

1.Same account(admin) is used in both shell and ssms
2.tried multiple things like import modules
3.XP_CMDshell works,but this returns null only for this query
4.I have tried to add -nowait,but that doesn't help as well

I have been trying to get this done from more than a day,but this doesn't work..
I have to use xp_cmdshell because writing a c# app is not allowed.Bat file doesn't help because server names are passed as a comma seperated list.

I am modifying the code and was told not to rewrite when asked

Please let me know if you need any further info

Repro:
Below is entire command from print,if you remove powershell.exe and -c ,below will run in powershell..

powershell.exe  -c "

$computers=Get-WmiObject -Class Win32_Volume 'servername'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label

$insertquery="
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           ('$pscomputername' ,
           '$name' ,
           '$capacity',
           '$freespace',
           '$Label'
           )

"

Invoke-SQLcmd  -query $insertquery -ServerInstance 'servername' -Database dbname

}

"


texts: Max allowed packet changed still replication not starting

texts:
Suddenly I notice my replication stopped and first I got some primary key duplication error when I checked using the show slave status\G. Then I remove those lines and restarted and ending up now with this error. So I first increase both the master and slave max_allowed_packet to 1G yet not working and now I changed to 2G yet is the same. I have run stop slave and
change master to master_host='192.168.**',master_user='repu1',master_password='***', master_log_file='mysql-bin.000039', master_log_pos=640109146;
start slave; yet I keep getting the same error. "Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'"

I have checked the repu1 password using this command mysql -h 192.168.* -u repu1 -p its working fine too.

show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.100.12
                  Master_User: repu1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000039
          Read_Master_Log_Pos: 640355070
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000039
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: dbn
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 640109146
              Relay_Log_Space: 247058
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary lo


texts: How to get a JSON array without JSON_ARRAYAGG in MySQL

texts:
I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT JSON_ARRAYAGG(column_name) FROM table_name;
I got to this piece of SQL: SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name; which  works almost like JSON_ARRAYAGG. The problem is when there is no rows it returns [null] instead of null.
What is the most efficient way of replacing a JSON_ARRAYAGG function with some equivalent? I would like to avoid something like this:
SELECT IF(
  (SELECT GROUP_CONCAT(column_name) FROM table_name) IS NOT NULL,
  (SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name),
  NULL
);


texts: Oracle SQL 11gR2 => find out all dates where "impdp" was performed

texts:
Hello my team performed several database Data Pump Import "impdp" over the last two years.

The workflow was always to import all "application" schemas ( The schemas that are used from our application) dailyBackup-2019-04-05.sql dump from one of the daily backups.

Now I need to find out the exact dates whe the "impdp"restores where performed.

I tried the following:

I found the file dpdump on my server but it only contains this three lines:

I only have one "import.log" file that got always overridden every time a restore with dpdump was taken.

Are there maybe system tables tables where I can check every time when a dpdump was performed

Where else can I look for the log some "import log" files?


texts: Can Cassandra prevent TTL from updating on upsert of table

texts:
I have a Cassandra table that is like this...

CREATE TABLE IF NOT EXISTS analytics.data(
    id double,
    data text,
    PRIMARY KEY(id, data)
) WITH default_time_to_live = 120 AND gc_grace_seconds = 60;

My understanding is inserts default to upserts. The problem is according to the docs, upserts still increase the TTL. Short of manually managing this, is there a way to turn off TTL on upsert and just have it updated on insert?

Possible Solution

Using TTL on the upsert allows overriding the table definition and TTL(data) allows us to get the existing TTL. Is a pre select query the only way to do this?


texts: Best practice between using LEFT JOIN or NOT EXISTS

texts:
Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?

What is benefit to using one over the other?

If none, which should be preferred?

SELECT *
FROM tableA A
LEFT JOIN tableB B
     ON A.idx = B.idx
WHERE B.idx IS NULL

SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)

I am using queries within Access against a SQL Server database.


texts: How to transfer DDL changes between different stages?

texts: What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?


texts: MySQL - Deleting old data; Select with index taking time

texts:
I am a bit confused about 2 queries.

First query I am running on a 50m rows table by index on a timestamp row with a limit, I would expect the query to be immediate regardless of the size of the table, since I run on an index with small limit. Am I wrong?

SELECT 1
FROM indicators.fileso fo
WHERE fo.lastSeen < NOW() - INTERVAL 2 MONTH
limit 100;

Create table:

CREATE TABLE `fileso` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hostId` int(11) unsigned NOT NULL COMMENT 'code',
  `sha256` binary(32) NOT NULL COMMENT 'meir',
  `fileName` varchar(150) DEFAULT NULL COMMENT 'meir',
  `fullPath` varchar(350) DEFAULT NULL COMMENT 'meir',
  `lastSeen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`uniqueness`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=1842469487 DEFAULT CHARSET=utf8

Explain:

Second query when I select by timestamp row with index, with order by desc limit 1 its immediate, but when I do order by asc limit 1 it takes alot of time, dont understand that.

Immediate query:

SELECT 1
FROM indicators.fileso fo
ORDER BY fo.lastSeen DESC
LIMIT 1;

Takes time:

SELECT 1
FROM indicators.fileso fo
ORDER BY fo.lastSeen ASC
LIMIT 1;


texts:
What is the exact difference between Oracle (+) and comma separate tables and join tables.
Somebody force me to use oracle (+) instead of left join. Is their any performance issue ? Because as i aware of these notions they are identical.


texts: What to consider when setting flush_interval_seconds in SQL Query Store?

texts:
Most of the settings for Query Store are fairlying straight forward.  But as best as I can tell the one most likely to cause performance issues (waits) and with the least clear documentation is flush_interval_seconds

I have read Erin Stellato's, SQL Server Query Store: Default Settings

  I’d also probably drop  DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.  This setting determines how often Query Store data is flushed to disk.  If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.

Microsoft's Best Practice with the Query Store does not even mention it.

Microsoft's sys.database_query_store_options (Transact-SQL) says (for on Premisis);

  Defines period for regular flushing of Query Store data to disk. Default value is 900 (15 min).

The above implies (possibly) Query Store data is held in memory until a regular 15 minutes flush occurs then it is pushed/flushed to disk

But the documents for Azure are slightly different. Operating the Query Store in Azure SQL Database

  Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk (emphasis is mine)

The Azure description implies Query Store data moves to disk as regularly as normal data, but if it has not moved by the age, then it is forced to disk.

I know that Query Store data is part of the database, I am not clear if logs are updated only when the data is pushed to disk, or in real time.

Some points I am not clear on that could impact the decision. (Maybe some of these should be separate questions?)

Is query store data written to the log?  If so would a restore let us recover any Query Store data, that had not been pushed to disk?
If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?
Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?
What does changing the flush_interval_seconds value really do?

I have this scenario in my mind where, there is a long running query using all the system resources for an hour+ and Query Store hit the 15 Minute flush interval and now it is in conflict with the users application for resources. I can't work out what the scenario really is and how it impacts the user application. How do I justify my decision for the value I set?


texts: Postgres: Trigger always uses the same value for now()

texts:
I would like to create a trigger that keeps a last-updated time on each row. It would fire on CREATE and UPDATE, and store the current time into a column. I'm following basically the exact recipe from here: https://www.postgresql.org/docs/9.2/plpgsql-trigger.html

When I run it, the value in the column is always the same, no matter how many times I update the field. It's as if 'now()' was only evaluated once, and the value was cached.

Here is my code:

CREATE FUNCTION alex_trigger() RETURNS trigger AS $alex_trigger$
    BEGIN
        NEW.v := now();
        RETURN NEW;
    END;
$alex_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER alex_trigger BEFORE INSERT OR UPDATE ON alex
    FOR EACH ROW EXECUTE PROCEDURE alex_trigger();

I must be doing something dumb. Any ideas?


texts: SQL Server transaction log reported full when it isn't

texts:
The transaction log for database 'WebDB' is full.

No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of space. SQL Server 2008 SP3, but not R2.

I'm a developer, we no longer have a DBA. So it falls to me to try and fix this.


texts: Would a cron job to automatically optimize tables on a monthly basis be ideal?

texts:
I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time.

My database is frequently using select, insert, update. It has about 45,000,000 rows.

Select uses the dateChecked column and update uses the URL column.


texts: What's included in the default OSX 10.8 install of Postgres?

texts:
I've seen several sites claim that Postgres is installed by default for OSX 10.7 and above and I see there's a psql command in /usr/bin/, but there doesn't seem to be a pg_ctl anywhere.

So is the default install of Postgres something that I can actually develop against or is it just there for OSX itself to use? I just don't want to install an extra version of Postgres and screw something up.


texts: Get top 5 students for each class in many-to-many relationship

texts:
So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.

students
student_id | name | email | GPA

classes
class_id | name | room

student_class_rel
student_id | class_id

I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?
For example, this query gets 100 classes and then gets every student_id for all students in that class
SELECT *,
    JSON_ARRAYAGG(student_id)
FROM classes
    INNER JOIN student_class_rel
    USING(class_id)
GROUP BY class_id;
LIMIT 100

I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.
Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.
And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).


texts: Automatically reject certain expensive query statements

texts:
I have some clients which sometimes run some "not allowed" very expensive queries.
I've tracked them down using Extended Events.
Is it possible to reject these queries automatically? I know the SQL statement so would be able to catch them using the "sql_text" event in EE.
But I haven't found a way to perform any actions in EE when e.g. a particular event is happening.
Or is there another way around this?


texts: MySQL failures after changing innodb_flush_method to O_DIRECT and innodb_log_file_size

texts:
We changed innodb_flush_method to O_DIRECT, innodb_log_file_size to a bigger value, and 3 other things (innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size) while MySQL was stopped. Unfortunately DirectAdmin restarted MySQL before the log files were deleted.

Later, when we tried to fix the situation and stopped MySQL (and stopped DA) we deleted log files it didn't help - databases were corrupted. We finally created a new instance, and recovered everything from backups.

We had errors like these:

2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425569713
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 3 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 28 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425542325

Can someone tell me what really happened? We've seen bad things happening when you change innodb_log_file_size and don't delete log files, but we were able to recover from it then.

Was setting innodb_flush_method to O_DIRECT the cause here?


texts: How do I migrate varbinary data to Netezza?

texts:
I got a warning message while migrating DDL from SQL Server to Netezza:

  Warning: [dbo].[spec_binarymessage].[blobdata] data type [varbinary] is not supported the target system and will be scripted as VARCHAR(16000).

I'm wondering whether this kind of data conversion will cause some issues such as truncation of data etc.?


texts: Mongodb: how to create multiple indexes on same field?

texts:
For example, I created this index:

db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$gt:10}}})

Index created successfully. Then I want to create second index:

db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$lte:10}}}

But above will give error  IndexOptionsConflict, but no error details, only dumps the index object.

I have tested to create such indexes on PostgreSQL and it works. How can I do this in MongoDB?


texts: SQL Server 2014 Batch Transaction Log Management

texts:
I have recently taken over a system which is going live in March on SQL Server 2014 after running on Sybase for years.  The system is 24 x 7 OLTP and heavy batch at night.  Prior years during batch in Sybase the transaction logs would fill up freezing the system.  I have not managed a system such as this and was looking for some advice.

I would propose sizing the transaction log appropriately and running log backups every 15 minutes.  Does this sound as though it would be the best procedure?  Also, how should I best size the log?  Should it be 2 x the largest transaction size?  What if the size of the transaction varies?


texts:
I'm using SQL Server 2008, and will migrate DynamoDB some tables.

I want to get I/O statistics of read bytes, and write bytes from table.

I don't know how to do this. What command should I use?


texts: How to return a CTE as REFCURSOR from an Oracle stored procedure?

texts:
I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.

My procedure looked like this:

Create or replace Myproc ( MyRefCursor  IN OUT SYS_REFCURSOR)
as
begin
    Open MyRefCursor for
        Select * from ...something ...;

end;
/

I rewrote this query:

   Select * from ...something ...;

As this CTE:

with MyCTE As (
    ... ;
)
Select * from MyCTE;

Opening a cursor this way does not work:

Open MyRefCursor for
with MyCTE As (
    ... ;
)
Select * from MyCTE;

Nor does this:

with MyCTE As (
    ... ;
)
Open MyRefCursor for
Select * from MyCTE;


texts:
We're trying to configure periodic jobs in PostgreSQL. To do this, we have installed on Linux machine, with Postgres 9.6 running, the citusdata pg_cron project.

System information:

OS: Linux pg 4.4.0-72-generic #93-Ubuntu SMP
PG: Postgres 9.6.3 installed from repo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'
Citusdata pg_cron project https://github.com/citusdata/pg_cron

Following the instructions in the pg_cron repository, we set in postgresql.conf the configuration below:

shared_preload_libraries = 'pg_cron'
cron.database_name = 'our db_name'

Then, on db_name, we created the EXTENSION pg_cron

CREATE EXTENSION pg_cron;

and we scheduled our first Postgres job:

SELECT cron.schedule('45 12 * * *', $$CREATE TABLE testCron AS Select 'Test Cron' as Cron$$);

So, jobid 1 is created and listed in table cron.job.

We expect that at 12:45 the command of the scheduled job will be launched. But nothing happens.

The testCron table is not created and we have no trace in any logs.

We have also defined LOG_FILE in /usr/src/pg_cron/include/pathnames.h to enable logging.

But, after re-compiling the project and restarting the Postgres service, we did not track log for pg_cron.

How can we enable logs for pg_cron to check scheduling result?

As work around, we use this in root's crontab:

su -c "Sql_statement" postgres

but this require full control of the Linux system.

It would be useful to be able to schedule jobs from the db server.

Can someone help us?

Thanks in advance!


texts: SQL Server 2012 Mirror in a Disconnected state - how do I fix it?

texts:
We had a hardware failure last night and we got our servers up and running.  After logging into SSMS I find all the mirrored databases in a disconnected state.  I've done some searching with google-fu, but I've not found what I need.

Can anyone point me in the right direction?

Edit#1

I broke the mirror and attempted to reconfigure it through the wizard. Once I "Finish" it looked like it was going to work, then it failed with the following error:

  The server network address "TCP://SeanServer00.DBA.com:5022" can not
  be reached or does not exist. Check the network address name and that
  the ports for the local and remote endpoints are operational.
  (Microsoft SQL Server, Error: 1418)


texts:
Problem: Need to account for delays in review process.
The field "EC2WL" is when an employee submitted a case to the Work Leader for review and the field "WL1stReview" is when the Work Leader first reviewed the case. If the difference between those two dates is greater than 7 days, the case is considered delayed. When a case is delayed, the amount of delay is calculated by subtracting 7 from the difference in days between the two fields.
Similarly, a case delay can happen at the section manager level. The fields to consider here are "FODReview" which is the date the case is submitted to the section manager, and "SM1stReview" the date the section manager first reviews the case.
I need these delays to sum in the field "CaseDelay" when I mailout a case.
I currently have a functioning ElseIf code that populates "CaseDelay" with a placeholder of "69" when certain conditions are met that would require a case delay to be calculated. I need to replace the "69" with a functioning piece of code.

Private Sub Mailout_AfterUpdate()
If Me.FODReview > #1/1/1980# Then
    Me.CaseDelay = "69"
Else
    Me.CaseDelay = ""
End If


texts: replication warning: transactional replication latency alert not working

texts:
I have set up a predefined replication alert (as outlined here) in SQL Server 2008 and the alert for Replication Warning: Transactional replication latency does not appear to work.  This is triggered by error 14161.

I found a number of posts around the web that indicated this was a bug, but the posts were so old, I'm not sure that it is still the case.

Is this still a broken feature?  If so, can anyone suggest a work-around?

EDIT/ADDITIONAL INFO: I see there are a number of scripts that have been highlighted in similar questions.  To refine my question, I'd like to confirm this is a bug and I am looking for a work around that is rather out-of-the-box... that is, just another way to write the SQL Server Agent alert to get it to work...


texts: Scalar UDF causing performance issues in SQL Server

texts:
I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect your performance much. Below is the part of my UDF code which I am trying to replace based on that.

WHILE @i <= @Maxid
 BEGIN
         SET @i = @i + 1
         SELECT @addrname = name
         FROM [dbo].[temp]
         WHERE id = @i

         IF CHARINDEX (@addrname, 'TEST STRING FOR TEST') > 0
            BEGIN
               SET @addrstart = CHARINDEX (@addrname, 'TEST STRING FOR TEST')
               SET @addrlen = LEN (@addrname)
               SET @i = 0
               BREAK
         END
 END

My dbo.temp table contains some sample char strings which I need to search. Temp table contains two columns id and name. It has following sample data in it:

1, #
2, TEST
3, STRING

I am looking to out to replace use of temp table in above code to see if it helps in improving performance. Please help me with that.

UDF basically creates some type of code from addressline and PO code fields and it needs to run for 5 million rows.


texts:
I have a VM where I use SQL Server 2008 as a black-box data store for a product I work with. I'm being told by my IT police that my VM is not (or will not be) secure in a short time unless I can enable TLS 1.2 for the service on port 1433.

My current understanding is that there is no way I can achieve this and I shall have to upgrade the SQL Server version to something like 2012 or higher... even then it might not work "out of the box" as there'll be configuration to perform.

Am I correct that SQL Server 2008 simply cannot be used with TLS 1.2?

As I say, I'm not a user of SQL Server in the expert sense, it's just there and I make use of it and I'm told that I need to be doing something with it.


texts: Data from more than 1 query - possible union needed?

texts:
I have a query (Query A) that pulls data from my Customer table.
I have another query (Query B) that pulls data from a list of records associated with various customers.
-   Every customer on Query B exists on Query A….but not every customer on Query A has a record on Query B

What I’m trying to do is to take 1 field from Query B, and pull it into Query A….but when I do that, it essentially limits the entire scope of Query A to only those customers present on Query B.

Is there a way that I can have Query A display it’s full list of customers, with a column next to it that displays data from Query B only if there is data on Query B?

I hope I’m making sense!