texts: Could not allocate space for object sql server

texts:
Can anyone give a solution for this error:

  Could not allocate space for object ‘object_name’ in database ‘database_name’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

How can I delete files from primary file group?


texts:
I have an OpenVPN network consisting of VPN server (Linux), client (win8) and SQL Server (win 2008 server, SQL Server 2005), I'm trying to (of course) connect client to server.

When I try to connect using SQL Server Management Studio, it displays an odd error:

  Connection Timeout Expired.  The timeout period elapsed during the post-login phase.
  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to cre ate multiple active connections.
  The duration spent while attempting to connect  to this server was - [Pre-Login] initialization=580; handshake=865; [Login] ini tialization=0; authentication=2; [Post-Login] complete=58013;

The same happens when I try to connect from my (.NET) application - just hangs until the ConnectionTimeout has been reached throwing the same exception.

Curious thing is that I CAN successfully connect using sqlcmd from client and sqlsh+jTDS from OpenVPN server. Nothing shows up in SQL Server error logs or event log, netstat tells that the connection is in established state. Firewalls are off and I have tried every concise mix of connection string parameters, network parameters tweaking and still nothing. I have even tried bouncing the connection off a vpn server - same results.

Any ideas?


texts:
I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same.

Login into the SQL Server Management Studio, Select Database Engine, \SBSmonitoring, Windows Authentication.
Go to Object Explorer--Security folder--Logins folder.
Right click on SA account and select the Properties option.
In General Page, change the SA password and confirm it.
In Status Page, change Login to Enabled. Click OK to save the change.
Restart the SQL Server and all its services.

Any idea ?


texts: Will increase in Innodb_buffer_pool_size increases insert query performance?

texts: We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G occupied by Myisam index tables. All at a sudden we observed there is increase in insert query execution time in one of the box "M2" causing replication delays. Upon checking Innodb_buffer_pool_size is defined as 10G in M2 but in remaining boxes the value is 128M. So is this impact with increase in the innodb_buffer_pool_size only? why ?


texts: Check Orders that can be delievered with reducing quantity from stock

texts:
Just like any retail business we have an Orders table and an Inventory table. What I am trying to do is to check Orders for which we have enough stock available to dispatch. A few things I need to consider:

If all the items in an order are available only then consider this order to be “Deliverable”

Check Order's deliverable status in the order of OrderID (int value) .i.e OrderID 1 then 2 and so on.

Before checking for deliverability of next order, reduce the available of stock for the next order. (not update the Inventory table but just take into account the stock quantity that has been already consumed by previous orders).

If we do not have enough stock for 1 or more items in the order, completely ignore the order and do not reduce available stock quantity for the next order to be checked.

In the following example:

Order 100 is fully Deliverable because we have enough stock for all products.
Order 200 is not fully Deliverable because PID 2 requires Qty 5 but we only have 3 left after 2 being consumed by the Order 100
Finally, Order 300 is also fully Deliverable because we have enough stock for all products.

Test Data
INSERT INTO @Inventory(PID, Qty)
VALUES  (1  , 10)
    ,   (2  , 5)
    ,   (3  , 2)

INSERT INTO @Order (OrderID, PID, Qty)
VALUES  (100 , 1 , 2)   --\
    ,   (100 , 2 , 2)   ----> This order is fully available
    ,   (100 , 3 , 1)   --/

    ,   (200 , 1 , 2)   --\
    ,   (200 , 2 , 5)   ----> This order is not fully available
    ,   (200 , 3 , 1)   --/     because of PID 2 only 3 QTY left

    ,   (300 , 1 , 2)   --\
    ,   (300 , 2 , 2)   ----> This order is fully available
    ,   (300 , 3 , 1);  --/

Expected Output
OrderID Status
100     Deliverable
200     NOT Deliverable
300     Deliverable

My Attempt
I know that it is far from the actual solution but I still wanted to share what I have been trying :)
WITH OrderCTE AS
    (
        SELECT DENSE_RANK() OVER (ORDER BY OrderID) AS OrderRN
            , OrderID
            , PID
            , Qty
        FROM @Order
    )
,CTE AS
(
    SELECT o.OrderID
         , o.PID
         , o.Qty
         , i.Qty - o.Qty AS QtyAvailable
         , o.OrderRN  AS OrderRN
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    WHERE o.OrderID IN (
                            SELECT TOP 1 o.OrderID
                            FROM @Order o
                            WHERE NOT EXISTS ( SELECT 1 FROM @Inventory i WHERE i.PID = o.PID AND i.Qty < o.Qty)
                            ORDER BY o.OrderID
                        )

    UNION ALL

    SELECT o.OrderID
         , o.PID
         , o.Qty
         , o.Qty - c.QtyAvailable
         , c.OrderRN + 1
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    INNER JOIN CTE c ON c.OrderRN + 1 = o.OrderRN AND c.PID = o.PID
    WHERE o.Qty <= c.QtyAvailable

)
SELECT *
FROM CTE


texts:
I'm new to sqlserver and i'm learning about t-sql cursor.

I've seen in a book called "t-sql user guide" an example of cursor declaration.

There is  a command to return more rows for each fetch:

set cursor rows 3 for authors_crsr

I've tried it in my sqlserver2012, but it did not work.
I tried searching in google but had not luck.

What's wrong with this command? Maybe this command has been deleted from the version  i'm using? (2012)

Thank you.


texts: What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

texts:
I'm in a situation where I want to get the minimum value from of 6 columns.

I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.

The first method is to use a big case statement. Here's an example with 3 columns, based on the example in the link above. My case statement would be much longer since I will be looking at 6 columns.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2
            Else Col3
            End As TheMin
From   MyTable

The second option is to use the UNION operator with multiple select statements. I would put this in an UDF that accepts an Id parameter.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

and

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

And the 3rd option I found was to use the UNPIVOT operator, which I didn't even know existed until just now

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from
        cte
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Because of the table size, and frequency in which this table is queried and updated, I am concerned about the performance impact these queries would have on the database.

This query will actually be used in a join to a table with a few million records, however the records returned will be reduced to around a hundred records at a time. It will get run many times throughout the day, and the 6 columns I am querying are frequently updated (they contain daily stats). I do not think there are any indexes on the 6 columns I am querying.

Which of these methods is better for performance when trying to get the minimum of multiple columns? Or is there another better method that I don't know of?

I am using SQL Server 2005

Sample Data & Results

If my data contained records like this:

Id    Col1    Col2    Col3    Col4    Col5    Col6
1        3       4       0       2       1       5
2        2       6      10       5       7       9
3        1       1       2       3       4       5
4        9       5       4       6       8       9

The end result should be

Id    Value
1        0
2        2
3        1
4        4


texts:
I Have a table with user_id and product_id.

For my purpose, a particular user is allowed up to 5 rows only.

To enforce this, I created such a SQL statement:

INSERT INTO the_table (product_id, user_id)
SELECT p, u FROM (SELECT 121 AS p, 40987 AS u)
WHERE (SELECT COUNT(*) FROM the_table WHERE user_id = 40987) < 5;

Now My Question:

without explicit locking\transaction,
Is in the case of two simultaneous command run,
When the number of rows before their run is 4,
It is possible that the count will be calculated for both commands before making the insertion - Then there will be 6 rows for same user_id?


texts: Why is it necessary to type "host" before the imp command when I'm using SQL*Plus to import a dmp file into Oracle?

texts:
In SQL*Plus, I'm connected to an Oracle 11g database. When I type

imp help=y

It gives me

SP2-0042: unknown command "imp help=y" - rest of line ignored.

But when I type

host imp help=y

it gives mehelp with the import utility.

All the guides I've seen online for importing say to just type imp, without the host part. Why do I need to say host before using the utility?


texts: How to extend the Central Management Server in SSMS?

texts:
I'm looking into Central Management Server and I'm a bit surprised there's no 'Custom Reports' node in SSMS to add things into. It seems like that's exactly where you would want to place something like that.

Failing that, is there any documentation on how to create SSMS add-ins that use the CMS server list?


texts: Automatic Page Repair without AlwaysOn or Mirroring

texts:
Do Microsoft SQL Server 2012 Enterprise regular databases (without AlwaysOn) have Automatic Page Repair in any manner?

I know from below, by incorporating AlwaysOn, Secondary replicas receive Automatic Page repair. Just inquiring if this feature is available to SQL Server databases without Alwayson?

Per MSDN documentation https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-2017 , it states

"Each availability replica tries to automatically recover from corrupted pages on a local database by resolving certain types of errors that prevent reading a data page. If a secondary replica cannot read a page, the replica requests a fresh copy of the page from the primary replica. If the primary replica cannot read a page, the replica broadcasts a request for a fresh copy to all the secondary replicas and gets the page from the first to respond"


texts: Do non-PostgreSQL database softwares use roughly the same "structure" for communicating with them?

texts:
Basically, I have developed a PostgreSQL-based application which "in theory" could have its database software swapped out, but probably would cause a million headaches if I actually attempted to. I'm trying to determine if the other SQL database softwares (I frankly don't care about non-SQL ones in the least, because they seem too different for me to bother with them in this life) have the following concepts:

"hostname"
"port"
"username"
"password"
"handle database" (such as "postgres", which must be used to connect when there is no other database or when certain operations are to be done to the actual database)
"database name"

I guess I'm fairy sure already about all the points except for the 5th. The concept of a "handle database" seems like it might be PG-only. If such is the case, I'm not sure how I should handle that, but I'm awaiting your answers before I make a decision.
I have a good mind to just forget about ever supporting other database softwares, but the way my system is structured basically forces me to at least try to "genericize" the communication with the database with functions called "database_" rather than "PostgreSQL_". (Even when the queries sent to these functions would only work on PG...)


texts: Is there a way to add a column to a table without affecting performance?

texts: How do you add a column to a table in production without affecting its performance?  Or is this impossible?  I'm asking about Postgres.


texts: Cannot Access New Installation of MySQL on an Amazon EC2 Instance

texts:
I installed MySQL server on an Amazon EC2 instance and started it. Seems like it started, but when I try to do mysql -u root I get this error.

mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using       password: NO)

I don't have a password for the root and don't know how to go forward.
How do I access the new installation?


texts: Do UPDATE statement performance affect indexed column(s) in Postgres?

texts:
I have a table which is having millions of rows. While doing update I'm using primary key column in WHERE clause and it uses index scan as per the EXPLAIN plan (within BEGINand ROLLBACK block). I have tried to update in both cases (with and without indexed columns in update query), however there is not much change in query cost.

UPDATE mytable set COL1='test', IDXED_COL1=123, COL2='test', IDXED_COL2=2343
WHERE ID=1 //ID is primary key (using btree index)

And

UPDATE mytable set COL1='test', COL2='test'
WHERE ID=1 //ID is primary key (using btree index)

Is there no impact if the Table and the indexes are huge?

Edit 1

#EXPLAIN UPDATE MyTable set C2='Test', C3=1, IdxdC1 = 1, C4='Test', IdxdC2 = 2 WHERE ID = 1234 AND C1=1

 Update on "MyTable"  (cost=0.43..8.45 rows=1 width=798)
   -> Index Scan using "MyTable_ID_PK" on "MyTable"  (cost=0.43..8.45 rows=1 width=798)
         Index Cond: ("ID" = '1234'::numeric)
         Filter: ("C1" = '1'::numeric)

#EXPLAIN UPDATE MyTable set C2='Test', C3=1, C4='Test' WHERE ID = 1234 IdxdC1 = 1 AND IdxdC2 = 2 AND C1=1

 Update on "MyTable"  (cost=0.43..8.46 rows=1 width=773)
   ->  Index Scan using "MyTable_ID_PK" on "MyTable"  (cost=0.43..8.46 rows=1 width=773)
         Index Cond: ("ID" = '1234'::numeric)
         Filter: (("IdxdC1" = '1'::numeric) AND ("IdxdC2" = '2'::numeric) AND ("C1" = '1'::numeric))

(Both UPDATE's WHERE clause are does same in my situation)


texts: Remote accessing a MySQL server for non-root user

texts:
I have a MySQL server installed and running on an Ubuntu server. I use a Win7 PC to remotely access the server and for my 'root' user this works fine, I can start up MySQL Workbench, configure the connection to use the 'root' user and successfully connect and manage the database server.
My son will be doing A-Level computing and I thought it'd be good for him to have access to the database to learn with. Obviously, I don't want him to access everything on the server so I created a user in his name and a database for him to use. I've granted privileges for create, update, insert, delete and selectto him on his database only:-

grant insert, delete, update, create, select on 'hisdatabase'.* to 'hisusername'@'%';

So far, so good.
The problem I'm having is that when I use MySQL Workbench to set up a connection using 'hisusername' I get an error and can't login. The error dialog is:-

Failed to connect to MySQL at [server ip address]:[port] with user [hisusername]
Access denied for user [hisusername]@[Win7Client ip address] (using password: YES)

This connection works fine if I specifiy 'root' as the user but not 'hisusername'.
As user 'root' works perfectly, I'm guessing I'm missing a configuration parameter for 'hisusername' that is preventing remote access. Does anyone have any idea what this might be?


texts:
I have an Execute SQL Task in my SSIS control flow that's backing up a number of databases before it proceeds to run the overnight ETL:

EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\master'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Metadata'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\NDS'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Staging'
GO
BACKUP DATABASE [DQ] TO  DISK = N'G:\Backups\DQ\DQ.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'DQ', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [master] TO  DISK = N'G:\Backups\master\master.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'master', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Metadata] TO  DISK = N'G:\Backups\Metadata\Metadata.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Metadata', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [NDS] TO  DISK = N'G:\Backups\NDS\NDS.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'NDS', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Staging] TO  DISK = N'G:\Backups\Staging\Staging.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Staging', SKIP, REWIND, NOUNLOAD,  STATS = 10

The intent is to only retain 3 days, and discard anything older.

The problem is that the .bak files just keep growing and growing for a week or two, until the G drive fills up and the Execute SQL Task fails, which aborts the overnight ETL.

It seems the .bak is apparently not discarding old backups; looks like I could go back all the way to the last time I deleted all the backups to free some disk space on that G drive:

The question is simple: it looks like my Execute SQL Task is doing it wrong. How do I fix it? Or is it something else?


texts: Getting InnoDb internal Errors on every query run

texts:
I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly.

2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."innodb_table_stats" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

I have checked the  "mysql"."innodb_table_stats" and mysql.innodb_index_stats tables are present but still getting these errors.

The Structures for both tables are given below:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) UNSIGNED NOT NULL,
  `sample_size` bigint(20) UNSIGNED DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) UNSIGNED NOT NULL,
  `clustered_index_size` bigint(20) UNSIGNED NOT NULL,
  `sum_of_other_index_sizes` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySql Server details:
Server type: MySQL
Server version: 5.6.19-1~dotdeb.1 - (Debian)

I would like add more details about same, some weeks ago I was facing the some issues with default location of Mysql, So; I changed the location of Mysql by using following steps.

Shutdown Mysql
Move All Mysql file inside old folder to new folder
Change the datadir variable inside config file my.cnf
Restart the MySql Server


texts: How to find DELETE and UPDATE statements that have been executed on a database?

texts:
I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time.

I came across this following query, but it generates an error message:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

The error message is:

  Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.

If I try:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

I get another error:

Msg 321, Level 15, State 1, Line 3
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

As far as I understand in my research of the topic it is not an easy task to achieve.

Is this query even the right approach?

SQL VERSION in question:

Microsoft SQL Server Standard Edition (64-bit)
Microsoft SQL Server Management Studio                      10.0.2531.0
Microsoft Analysis Services Client Tools                        10.0.1600.22
Microsoft Data Access Components (MDAC)                     6.1.7600.16385
Microsoft MSXML                     3.0 6.0
Microsoft Internet Explorer                     8.0.7600.16385
Microsoft .NET Framework                        2.0.50727.4984
Operating System                        6.1.7600


texts:
I'm trying to identify an issue I sometime experience on a stored procedure.

I have to say that the stored procedure uses an UDF in the select clause (perhaps that's the cause of the problem) but anyway, whether it's not, here below a part of the execution plan:

where both the output estimated rows of the Hash Match and the estimated of the Index_Seek on the non clustered index IX_Job_OperationID are completely wrong:

I've tried to update the statistic related to the index IX_Job_OperationID, even with fullscan option, but it didn't help.

UPDATE STATISTICS Job [IX_Job_OperationID] with fullscan

I'm also using the recompile option in the stored because given the parameters provided the dataset involved may change a lot.

Can someone point me to the right direction and help me understand why even though statistics have been updated the Estimated values are so far from Actual?

Here a link to the actual execution plan

This execution plan causes a total of 8,610,665 logical reads, while other better plan that somehow are chosen can be around 122,692


texts: How to decrypt aes-128-ecb data in postgresql?

texts:
I am looking for a way to decrypt aes that was encrypted earlier in mysql and then migrated to postgresql. In mysql I used standard aes_encrypt with block encryption mode aes-128-ecb, but I can't seem to be able to decrypt that in postgresql.

I am currently trying my luck with the decrypt function from pgcrypto module of postgresql 12, but I cannot make it to use 128-bit key length. The documentation listed aes-128 as possible, but didn't say how to enable it.

I was trying CAST(decrypt(cc_data, '...key here...','aes-ecb') AS VARCHAR but this shows wrong result. Choosing 'aes-128-ecb' results in 'No such cipher algorithm' error.


texts: SQL Server 2012 patching failure - The language of SQL Server instance MSSQLSERVER does not match the language expected by the SQL Server update

texts:
I've picked up the support for a SQL Server 2012 instance on a server at our German site and need to apply SQLServer2012SP4-KB4018073-x64-ENU.
The patching is failing with the installer showing error

The language of SQL Server instance MSSQLSERVER does not match the language expected by the SQL Server update. The installed SQL Server product language is German (Germany) and the expected SQL Server language is English (United States).

The server properties general tab (in SSMS) shows Language German (Germany), but the SERVERPOPERTY('LCID') shows 1033 (US_English). The registry entry shows 1031 (German) as the install language (https://www.sqlserverscience.com/basics/localization/language-used-sql-server-binaries/#comment-191)

Trying to use the German patch file SQLServer2012SP4-KB4018073-x64-DEU results in

This SQL Server setup media does not support the language of the OS, or does not have the SQL Server English-language version installation files. Use the matching language-specific SQL Server media; or install both the language specific MUI and change the format and system locales through the regional settings in the control panel

Any pointers as to a way forward so I can apply the patch?
Attempted patching after first suggested fix


texts:
How can I remove the Read-Only Routing URL in an availability group.

I have set the Read-Only routing URL and I'm trying to get it back to how it was before my test and I can't find a way to remove those URL.

I tried with :
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''));
but SQL does not allow an empty value here.

Thank you for you precious help.


texts:
I need to create post deployment script which will upload data from .csv file to my database. But I can't understand how can I do this.

When I tried to deploy my project on my server I receive this error messaege:

  (2354,1): SQL72014: .Net SqlClient Data Provider: Msg 4861, Level 16, State 1, Line 9 Cannot bulk load because the file ".\Data\DataFile.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).
  (2346,0): SQL72045: Script execution error.  The executed script:

Could someone help me please?

P.S. Below you could find my examples of files.

Structure of my SSDT project:

Source
    Data
        DataFile.csv
    Script
        PostDeployment
            DataFile.sql
    PostDeployment.sql

DataFile.sql:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t
    (
        Column NVARCHAR(100),
        Column2 NVARCHAR(100)
    )
;
BULK INSERT #t
FROM '$(DataFile)'
WITH (CODEPAGE = '1251', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', DATAFILETYPE = 'widechar')

SELECT * INTO table1 FROM #t AS tcbm

PostDeployment.sql

:setvar DataFile .\Data\DataFile.csv
:r .\Script\PostDeployment\DataFile.sql

If I change :setvar DataFile to ..\..\Data\CarBrandModel - deployment also will be failed with the same error.


texts: How to output more than 4000 characters in sqlcmd

texts:
I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this:

restore database [DB]  from disk='F:\FULL1.bak' with norecovery
restore database [DB]  from disk='F:\DIFF1.bak' with norecovery
restore log [DB]  from disk='F:\log1.bak' with norecovery
restore log [DB]  from disk='F:\log2.bak' with norecovery
...
restore log [DB]  from disk='F:\logN.bak' with norecovery
restore database [DB] with recovery

Througout the SP, the lines get added to a parameter @sql varchar(max) which, at the end, gets printed with print(@sql), and if the user specified the right flag,  gets executed with exec(@sql). Both work perfectly. However, when calling the SP with sqlcmd like this:

set restoresql=exec restoredatabase @DB='Databasename', @Execute='y'
sqlcmd -h-1 -S SRV\T2 -U sa -P sa -d master -Q "%restoresql%" -o output.txt

The output file output.txt will always have a maximum of 4002 characters.

I've tried adding -y 8000 but that doesn't help. How can I get the output file to give me the full generated script?


texts: how to list all features installed in a sql server instance?

texts:
There are other Installation related questions with similar details here and here.

I could not find anything related to listing the installed features in sql server 2016 though.

I can do it manually looking at the logs - as per the info below, but I would like to automate it.

After installing SQL Server 2016 I get a log file - located somewhere in these folders:

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20200109_205540

A file which the name starts with Summary_ +my_server_name

in that file, amongst other things I can find a list of the Sql Server Features installed:

After installation, later on when the systems are already in use, logins and permissions applied, firewall fixed, etc.

Is there a simple way to get hold of the list of features installed on a sql server instance?


texts: Count missing values in each column of the table in PostgreSQL

texts:
Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious.
SELECT
    SUM(CASE WHEN ticker IS NULL THEN 1 ELSE 0 END) AS ticker_null_num,
    SUM(CASE WHEN profits_change IS NULL THEN 1 ELSE 0 END) AS profits_change_null_num,
    SUM(CASE WHEN industry IS NULL THEN 1 ELSE 0 END) AS industry_null_num
FROM fortune500;

And also performed the following query for each column individually:
SELECT count(*) - count(ticker) AS missing
  FROM fortune500;
 etc....

My Question:
Is there any better/dynamic way of doing it because this approach is very cumbersome and it will take a lot of time as I have like 50 - 60 columns in a table then what should I do in that case instead of this manual approach. Can somebody help me find the missing values of each column sorted in a descending order with a good method? Like for example:
COLUMN_NAME    MISSING_VALUES_COUNT
col1               60
col2               50
col3               45
col4               40
etc.....


texts:
I have a query in laravel which is working fine.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  'downloads.is_download' )
        ->orderBy('subjectID')
        ->get();

There is only one issue that is_download comes null when there is no relative entry in table. After some research I found that there is a function IFNULL by using which I can change is_download null to 0. So here is my query which is not working. Blank screen is showing.

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();

I am able to write this query in my phpmyadmin but not know how to write in laravel

This is the api, So whole code looks like

use Illuminate\Database\Query\Expression as raw;
use project\Models\Subject;
use project\Models\Semester;
use project\Models\StudentRegistration;

$app->get('/api/getSubject/:studentID', function($studentID) use ($app) {
error_reporting(E_ALL);
    $currentUser = StudentRegistration::where('studentID', $studentID)->first();

$subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id')
        ->where('universityID', $currentUser->universityID)->where('semesterID', $currentUser->semesterID)->where('courseID', $currentUser->courseID)
        ->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',  IFNULL( `downloads`.`is_download` , 0 ) )
        ->orderBy('subjectID')
        ->get();
print_r($subjects);
    return $app->response->write(json_encode([
                'error' => 0,
                'subjects' => $subjects
    ]));
});


texts: Restore SQL Server 2016 - buffer memory - OS memory

texts:
When restoring SQL Server 2008R2 DWH-DBs I always gave some memory back to the OS to speed it up.
Now I got performance problems restoring to SQLServer 2016:
Where does the restore buffer come from?
SQL Memory Manager I guess?
Does the SQL Server need more memory or the OS to restore big files over the network?
Backup is compressed and consist of 14 files each larger 2,5 TB
=> DBsize is lager 100 TB.
Parameter:

MAXTRANSFERSIZE = 4194300, BUFFERCOUNT = 5000, BLOCKSIZE default.
INSTANT FILE INITIALIZATION on, Network SMB, Server 2012R2


texts: Need help installing ODAC 122011x64 so I can connect ADW to Power BI

texts:
So I have successfully installed Oracle DB 19c and connected it to Oracle SQL Developer. I am able to create tables and run queries. Furthermore, I can connect to ADW with both python and SQL Developer and any changes that I commit are seen in all locations. I am trying to connect the ADW database to Power BI. I am running all this on a personal Windows 10 Pro laptop with plenty of SSD & RAM.
In a new window of Power BI I click Get Data and then Oracle Database followed by Connect. When I do this I get a pop up that says:
The recommended provider ('Oracle.DataAccess.Client') is not installed. You can continue with your current provider, however it has been deprecated and may not work correctly.

I clock OK. Enter my Server as the TNS Name that I have used for my python and SQL Developer instances. In my ADW database, I have a table called TEST. It's a simple table with 1 column with the years 2015 through 2020 in it. Been using this to test to see if things are working. So I click on the DirectQuery and run the SQL statement SELECT * FROM TEST under the Advanced options menu and select OK. A sign in window pops up and I select the Database table and enter my correct ADW username and password. The same one that I can use to access this table from SQL Developer and then select Connect. When I do, I get this error:
Details: "The provider being used is deprecated: 'System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.'. Please visit https://go.microsoft.com/fwlink/p/?LinkID=272376 to install the official provider."

So I go to the website and download the ODAC122011_x64 file from Oracle and unzip it. When I double click the setup.exe file inside the directory I get a yes/no user control account menu and I select yes and then a command prompt window flashes very quickly and then nothing happens. I ran a super slow motion capture from my phone to capture what the command window showed and below is what I see:
Starting Oracle Universal Starter...
Checking monitor: must be configured to display at least 256 color Higher than 256 . Actual 4294967290   Passed
Preparing to launch Oracle Universal Installer from C:\Users\<my username>\AppData\Local\Temp\OraInstall2020 09:20_09-04 {???}
Please wait .._

It does nothing after this I have tried running the oui.exe file under the install subdirectory and get nothing and I have tried running the install.exe file in Oracle Universal Installer that I acquired when installing Oracle DB 19c and I get the following error when I target the install.exe file for installation:
OUI-10118:C/Users/{my username}/Downloads/ODAC122011_x64/Setup.exe is either a directory or a corrupt file. Specify a valid filename.

This is a file that I just directly downloaded from Oracle's website and unzipped to a directory with no spaces in the directory location. I have the latest version of Java installed (JDK 15).
I am not sure the proper work around here. Why can't I get my Power BI to connect to my Oracle ADW table using the methods described in this article. Those are the instructions that I used. It appears that I need this ODAC application but I don't understand why it won't install.
Can anyone help me resolve this issue? Thanks!
Edit: I am using 64-bit on each application.
Update: Still having issues. I checked the log and this is what I am seeing:
Using paramFile:
C:\Oracle\Oracle_PowerBI_ODAC\ODAC122011_x64\install\oraparam.ini

Checking monitor: must be configured to display at least 256 colors.    Actual 4294967296    Passed

The number of files bootstrapped for the jre is 0.

The number of files bootstrapped for the oui is 0.


texts: Is there any trick to Resize LVM online that raw by UDEV rule without filesystem?

texts:
Here is the Logic Volume for example:

   lvcreate   -L2G -n Lv_OraD1 vg_db
   lvcreate -L180G -n Lv_OraD2 vg_db

   lvdisplay /dev/mapper/Lv_OraD*

UDEV rule to raw the LV:

   vi /etc/udev/rules.d/60-raw.rules

   ACTION=="add", ENV{MAJOR}=="253",ENV{MINOR}=="3", RUN+="/bin/raw /dev/raw/raw1 %N"
   ACTION=="add", ENV{MAJOR}=="253",ENV{MINOR}=="4", RUN+="/bin/raw /dev/raw/raw2 %N"
   ACTION=="add",KERNEL=="raw[1-9]",OWNER="grid",GROUP="dba",MODE="660"

   start_udev
   raw -qa

ASM Diskgroup maybe like this:

   CREATE DISKGROUP DATA1 NORMAL REDUNDANCY
   FAILGROUP Fg1 DISK '/dev/raw/raw1' SIZE 2048M ,'/dev/raw/raw2' SIZE 184320M;

The effect: the size up 2G of DISKGROUP DATA1 cannot be used.
so any tricks to achieve this result online?

   lvreduce -L90G /dev/mapper/Lv_OraD2
   lvextend -L90G /dev/mapper/Lv_OraD1

Thanks.


texts: Counting number of total results for each column name

texts:
I have a query. I just would like to get the number of orders for each distinct customer.
But the results give me very high numbers, most probably somehow summing up all ids. What am I missing?

SELECT
  COUNT(b.order_id) AS number_of_orders_for_each_distinct_customer,
  a.customer_id
FROM messages a
  INNER JOIN orders b ON a.customer_id = b.customer_id
  INNER JOIN order_items c ON c.order_id = b.order_id
  INNER JOIN products d ON c.product_id = c.product_id
GROUP BY a.customer_id


texts: Is there a way to schedule AUTOMATIC backups for specific tables in MySQL Workbench?


texts:
I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader).

I managed to connect and things are going almost fine.  I use mysql_real_connect and mysql_errno for connecting and capturing possible errors.  I'm using the InnoDB engine and STRICT_TRANS_TABLES in SQL mode.

Questions / Doubts:

I thought mysql_errno only captures errors and not warnings, but I'm getting a 1264 MySQL warning in metatrader (1264 SQLSTATE: 22003 (ER_WARN_DATA_OUT_OF_RANGE)), however it doesn't say where. How can I resolve this?
If I try to manually UPDATE or INSERT against my database I get no warnings and/or errors.

I would like to know if this is a libmysql bug or find out what's going on.  Perhaps there is a specific table/field responsible?

Any pointers?


texts:
I have three servers Server1, Server2 and Server3. Server1 contains DB1, DB2 and similarly Server2 Contains DB3 and DB4. Can I use Server3 to host the AG groups of Server1 and Server2.

I have drawn a very bad picture below my apologies :).
AG1 and AG2 represents availability groups. Is the below possible?


texts:
Since the upgrade to the 19C Oracle DB (before 12C) I receive randomly those ORA-03114 error message's.
Before the upgrade I never received it. Any idea's what exactly why those error messages pops up suddenly and what changes needs to be done?

Domain Win10 PC
Not all PC's in the domain having this issue
only localy tnsnames.ora change

ORA-03114: not connected to ORACLE


texts: How come website be random slow? Open sleep processes?

texts:
My website is randomly very slow by high MYSQL CPU, and I have no idea what the problem is. Hopefully I will come closer to the solution with this post.

If I check the mysql processes, I see a lot of open sleep commands when the website is slow. When there are none open sleep commands, there is no problem. So probably the problem lies there.

Curiously, this problem only happens sometimes. Sometimes I have no problems for 4 weeks, now the website is already 3 days in a row slow.

I already set the time_out mysql settings to 30 secs, but that does not help to solve the problem. So now I try to figure out what the problem is. Here are the alert status variables. Perhaps that gives some extra insights.

Could it be spambots/brute-force attacks?
Heavy/slow query's in the website?
Bad server settings?
Sql injections?

Other things that this problem can cause? What is the best way to tackle this problem now? Sending any direction will already help.


texts: SQL Server Linked Server error and Query Problem

texts:
I have two servers running SQL Server 2000 and SQL Server 2005.
There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database.

I have a linked server pointing from SQL Server 2005 database A to SQL Server 2000 database B. Here the scenario is I can able to access database SQL Server 2000 database B from SQL Server 2005.

Two issues I face I am randomly getting the error:

  OLE DB provider "SQLNCLI" for linked server "SQL2000" returned
  message "Communication link failure". Msg 10054, Level 16, State 1,
  Line 0 TCP Provider: An existing connection was forcibly closed by the
  remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for
  user 'NT AUTHORITY\ANONYMOUS LOGON'.

How to resolve this error?

Also, when the user want to access the database located in the same server what must be done? I mean accessing the database SQL Server 2005 database A and SQL Server 2005 database A i.e ignoring the linked server.


texts:
Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is:
SELECT * FROM B INNER JOIN A ON B.a_id = A.id;


texts: mongodb cluster Data distribution is not uniform

texts:
The chunk seems to average

{  "_id" : "flanker",  "primary" : "rs11",  "partitioned" : true }
        flanker.bill
            shard key: { "_id" : "hashed" }
            unique: false
            balancing: true
            chunks:
                rs1 73
                rs10    73
                rs11    73
                rs12    73
                rs13    73
                rs14    73
                rs15    73
                rs2 72
                rs3 72
                rs4 72
                rs5 72
                rs6 72
                rs7 72
                rs8 72
                rs9 72
            too many chunks to print, use verbose if you want to force print
but the Quantity and size are not same
node 10:
rs10:PRIMARY> show dbs
admin            0.000GB
bjc_papa         0.351GB
flanker         35.605GB
hades            0.162GB
local           19.968GB
za-ordergather   0.000GB
rs10:PRIMARY> db.bill.find().count()
12730327
rs10:PRIMARY> db.claim.find().count()
4889074
rs10:PRIMARY> db.commissionBill.find().count()
7657692
rs10:PRIMARY> db.endorsement.find().count()
7279615
rs10:PRIMARY> db.policy.find().count()
1729638
any other node, for example:
rs2:PRIMARY> show dbs
admin           0.000GB
bjc_papa        0.453GB
flanker         3.889GB
hades           0.152GB
local           6.441GB
za-ordergather  0.000GB
rs2:PRIMARY> db.bill.find().count()
3971029
rs2:PRIMARY> db.claim.find().count()
1969355
rs2:PRIMARY> db.commissionBill.find().count()
1521794
rs2:PRIMARY> db.endorsement.find().count()
951906
rs2:PRIMARY> db.policy.find().count()
1789974

Can anyone tell me why, Which makes me very confused？thanks


texts: Excluding Read only Log backups using ola-hallengren script

texts:
I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries?

The is_read_only column in sys.databases does not get set on secondaries, so that is not an option.

This link talks about the is_read_only column but is not authoritative enough.


texts:
I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4).

well, here's the command:

  pg_dump -f file.sql -F plain --inserts -n schema database

Ok. My command finished up really nice, but, it's not dumping everything to a schema called schema (in the example above). It's loading everything into public.

EDIT:

To make the question clearer:

The dump works fine. Upon inspection, it's dumping my schema.table, for example, to public.table. I want that to restored to schema, not public.

Any tips on solving this?

Thanks


texts: Create Nested XML in a Sql Server Indexed (Materialized) View

texts:
This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this:

SELECT Col1,
       Col2,
       ( SELECT Col3, Col4
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE;

(Using a subquery)

This is exactly at odds with the requirements of an Indexed View (no subqueries allowed).

Is there anyway to get these two features together?  (Have an indexed view that has an XML Tree?)

The short of my question is: How can I create an XML Tree (ie nested XML nodes) from many tables without using any subqueries or unions.  (So it will work with an SQL Server Indexed View.)

FYI: Not sure if it matters, but my specific query has over 10 levels of my xml tree (pulled from a bunch of different tables).


texts: should we replace some non-clustered indexes with clustered indexes

texts:
We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk.

I am thinking that we could replace these with one fk index since we never really access the tables by the id only the fk.

for example two tables

reportdata
(id bigint,
 itemid bigint
 ...
)

reportdatacosts
(
 id bigint,
 reportdataid bigint,
 decimal cost,
 ...
)

as I see it reportdatacosts has no need for an index on id because it is always accessed by the reportdataid so this is a perfect candidate for changing to just use clustered index. So I should change this to be a clustered index on reportdataid then id, right?

The table reportdata needs the id to be indexed for the fk on reportdatacosts to work so for this I am unsure. I believe we could perhaps get benefits by removing the fk and because this table is only populated in one place in the code it should not get bad data in. So if we remove teh fk and change to just the clustered index is there any drawback. Could we expect much benefit or might this give worse performance?

Edit: I'm trying to understand when a clustered index would not be just on the pk - this is the only example I can think of


texts:
New to DB2 on AIX currently running V9.7

Using TSM to backup the database with the command:

db2 backup db (db name here) online use tsm include logs

When I run the backup it finishes without error.

Using db2adutl query, I see the FULL DATABASE BACKUP information but I see nothing under LOG ARCHIVE:

Retrieving FULL DATABASE BACKUP information.

    1 Time: 20150305104339  Oldest log: S0000664.LOG  DB Partition Number: 0    Sessions: 2
    2 Time: 20150305100030  Oldest log: S0000662.LOG  DB Partition Number: 0    Sessions: 2
    3 Time: 20150305073135  Oldest log: S0000660.LOG  DB Partition Number: 0    Sessions: 2

Retrieving LOG ARCHIVE information.

  No LOG ARCHIVE images found for (db name here)

I have the following db archive parms set:

 (LOGARCHMETH1) = TSM

(LOGARCHCOMPR1) = OFF

  (LOGARCHOPT1) =

(TSM_MGMTCLASS) =

 (TSM_NODENAME) =

    (TSM_OWNER) =

 (TSM_PASSWORD) =

What am I missing?


texts: Does dumping/restoring do the same as "vacuum full"?

texts:
I have a large database running with PostgreSQL v11, and the disk space is very limited, so I have to VACUUM (FULL) the database frequently (e.g. every day).

But vacuuming is a very time-consuming operation. Furthermore, I often encounter vacuum error message such as

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

Fortunately, the service can be shutdown at a particular time point of the day, so I can dump the database after shutting it down, restore it immediately and restart the service again.

In addition, I found that "dumping/restoring" is faster than "vacuuming".

My question: Do the things that are done by "dumping/restoring" include the things what are done by "vacuuming full"?

If so, I can do it instead of vacuuming.


texts: Sub-select takes "ages" - EXCEPT is much faster

texts:
Script to create the tables

DROP TABLE IF EXISTS history;
CREATE TABLE history (
    id integer NOT NULL,
    ticket_id integer NOT NULL);
ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY KEY (id);
CREATE INDEX history_ticket_id ON history USING btree (ticket_id);
DROP TABLE IF EXISTS ticket;
CREATE TABLE ticket (
    id integer NOT NULL
);
ALTER TABLE ONLY ticket ADD CONSTRAINT ticket_pkey PRIMARY KEY (id);

Dummy data

INSERT INTO history values (generate_series(1, 30000), generate_series(1, 30000));
ANALYZE history;

INSERT INTO ticket values (generate_series(1, 40000));
ANALYZE ticket;

Query with sub-select

explain analyze select distinct ticket_id from history
       where ticket_id not in (select id from ticket);

explain analyze slow sub-select

     HashAggregate  (cost=15510545.50..15510695.50 rows=15000 width=4) (actual time=170892.668..170892.668 rows=0 loops=1)
   ->  Seq Scan on history  (cost=0.00..15510508.00 rows=15000 width=4) (actual time=170892.644..170892.644 rows=0 loops=1)
         Filter: (NOT (SubPlan 1))
         Rows Removed by Filter: 30000
         SubPlan 1
           ->  Materialize  (cost=0.00..934.00 rows=40000 width=4) (actual time=0.006..2.685 rows=15000 loops=30000)
                 ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.038..21.347 rows=30000 loops=1)
 Total runtime: 170892.965 ms

Query with EXCEPT

explain analyze select distinct ticket_id from history
       except select id from ticket;

explain analyze with EXCEPT

HashSetOp Except  (cost=0.29..2449.29 rows=30000 width=4) (actual time=41.641..41.641 rows=0 loops=1)
   ->  Append  (cost=0.29..2274.29 rows=70000 width=4) (actual time=0.024..27.835 rows=70000 loops=1)
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.29..1297.29 rows=30000 width=4) (actual time=0.024..14.527 rows=30000 loops=1)
               ->  Unique  (cost=0.29..997.29 rows=30000 width=4) (actual time=0.022..10.856 rows=30000 loops=1)
                     ->  Index Only Scan using history_ticket_id on history  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.021..6.031 rows=30000 loops=1)
                           Heap Fetches: 30000
         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..977.00 rows=40000 width=4) (actual time=0.018..8.364 rows=40000 loops=1)
               ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.018..3.808 rows=40000 loops=1)
 Total runtime: 41.702 ms

DBMS version

PostgreSQL 9.3.10

Questions

Why does one take much longer than the other?


texts: MySQL Error: “Table storage engine for `proc` doesn't have this option (ERROR 1031)”

texts:
I'm running into a strange problem with MySQL. Searched everywhere for a fix but cannot find anything. Running Ubuntu 14.04.

I have a database running on Percona MySQL 5.7.18-16. I want to update this to Percona Server 5.7.19-17. I run a script that performs a mysqldump through Ruby on Rails in a Rake task.

When I use the updated Percona and get to the mysqldump stage, I get the error as stated in the title. The table that it refers to is the internal mysql.proc table. I have no idea why this is occurring.

I noticed that if I run mysql_upgrade --force before the dump, it works (although mysql_upgrade runs into the same error but seems to ignore it). I do however want to get to the root cause so that I do not have to run mysql_upgrade. I ran a diff on my database .sql files before and after mysql_upgrade, and there was no change.

Does anybody have any ideas as to how to get to the root of this problem and fix it? Any and all suggestions will be greatly appreciated!

Thank you!


texts:
My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for further execution.
So every time i query the plan cache and i see like 50+ plan(i search for particular view in the  query text to find out the queries) and to find the serial plan i have to open Query plan and see whether it is parallel or not.

SELECT
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.creation_time,
cp.execution_count AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
where
st.text like '%FormSearchModel%'
--AND qp.query_plan.exist('//PhysicalOp="Parallelism"') = 0
--AND n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
order by creation_time desc

It tried using query_plan.exist('//PhysicalOp="Parallelism"') = 0 to find the serial plan and it is not working.
How can i achieve this.?Any suggestion.


texts:
From that StackOverflow's question:

Summarizing:

My CRUDs are: customers, employees and branches.
Customers and Employees are associated with one Person, one Individual (fields related to the person itself) and one User (for login purposes). So Individuals and Users are always related to one Customer or Employee person.
Branches are associated with one Person and one Company (fields related to the corporate person). So Companys are always related to one Branch person. They didn't have an user because they're affiliated to the company that this database stands for - it's their employees who can authenticate into the app.
An Employee belongs to one Branch. A Branch has zero or many Employees. (yeah, diagram is incorrect, sorry!)
Currently I'm using a single bit to diff Employee role - I've employees with operational and administrative rights. Roles permissions goes in the code itself, and here you can realize that customers also have their own role... Any suggestions to implement a better (and simple) way to manage that roles for both customers and employees?

How should I change that wrong 3NF structure to achieve a well designed single table inheritance for a better use of MVC pattern?

Update

Ok, I tried to simplify it a bit, and now I accomplished this new structure:

Can I still improve it? How?


texts: MySQL/JDBC: Can a commit on server get missed on client?

texts:
I am wondering about when conducting a transaction of one or two updates/inserts from a JDBC client to MySQL Server over the network...

In the event of losing the network during the commit, can I get stuck with the commit occurring on the server, but not being known to have happened on the client?


texts: Fetch 10 paginated ranked posts of each category

texts:
Posts table as p:

| id | cid | score | ...
--------------------
|  1 |  1  |   3   | ...

Categories Table as c:

| id |...
--------------------
|  1 |...

What i need? Reterive paginated ranked posts in each category.

What i did do:

SELECT
    DENSE_RANK() OVER (
    PARTITION BY c.id
    ORDER BY p."score" DESC NULLS LAST) AS ranking,
    p.id,
    count(*) OVER() AS full_count
FROM p
INNER JOIN c ON c.id=p.cid
offset 20 limit 10

This query will return whole records and affect offset/limit on it. But i need to affect offset/limit on each category, separated.


texts:
Running MAMP (not PRO) 2.0.5, MySQL 5.5.9, locally on OS X and I cannot get MySQL to read a my.cnf file.

I've tried throwing it in the following directories with no results:

/Applications/MAMP/conf
/Applications/MAMP/Library
/Applications/MAMP/db/mysql

On each attempt, I make sure to shutdown the server, move the file and server is then restarted, after which a quick check of SHOW VARIABLES reveals no effect.

I'm using MAMPs my-small.cnf, renaming as my.cnf before copying over.

Any ideas as to what's going on?


texts:
I want to fetch some persons data from two tables via equi join. Some persons have address data and some have not. For those persons who haven't the addresses_id from table persons is NULL.

For all persons who have an address I do (query#1):

SELECT p.last_name, p.first_name,
       a.street, a.house_nr, a.post_code, a.city
  FROM persons AS p, addresses AS a
 WHERE p.id=42 AND a.id=p.addresses_id;

For all persons without address data I do (query#2):

SELECT p.last_name, p.first_name
  FROM persons AS p WHERE p.id=42;

Is there any smooth way to put these two queries into one. Maybe with an if-clause? I don't want to check the addresses_id field before by myself and then execute query#1 or query#2.

I'm using MySQL with InnoDB engine.
Thanks.


texts: Select most recent row by some other column

texts:
I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors.  I've seen a lot of people want to do the same thing online, and a lot of different proposed solutions, but most seem overcomplicated, and I can't get anything to work properly or do what I want.

I have data in a table where a particular thing (Room) will have a name, and a most recent status (AnalogValue; it correlates to online status) with a corresponding date/time (LogTimeStanp).

I'm most interested in the current online status for each room, so I just need the most recent row for each room.

There can be a number of rooms, for an example 1 - 5,000 (there's no imposed upper limit).
There is a lot of data that pulls back when I look at the historic online
status.
There is other data in this table that I'm not interested in for this query.
I have to join tables to get a readable room name, otherwise I just have a GUID.
I'm not sure if I need to use MAX(), SELECT DISTINCT, Group by, or something else.  I have not been able to get these to work to my satisfaction.

This code does not work, but should give you an idea of what I want to do:

SELECT r.RoomName, a.AttributeID, a.AnalogValue, max(a.LogTimeStamp)
  FROM CRV_AttributeLog a join CRV_Rooms r on a.RoomID=r.RoomID
  where a.AttributeID like 'online_status'

My intent is to get the AnalogValue with the max (most recent) LogTimeStamp per individual room.

I have gotten a query to run with group by, but it doesn't seem to be grouping it (and certainly not by room name), so I guess I am doing it wrong.

Update 1, 2, 3, 4

Added pics for context.  Also, this query will form the basis of a report, so it has to be entirely referential, that's why I just want "the latest" online status.  Further complicating things, "the latest" may not have happened today, this week, or this month.  But it's in there, somewhere, sometime.

Each row in CRV_AttributeLog will have its own AttributeID and AnalogValue, and the row is made unique by the time stamp (LogTimeStamp).  Otherwise, it's entirely possible for AttributeID and AnalogValue to be matching across rows (and that's OK).  This table is logging information about when a few different somethings change: online status, if there is an error, etc.  ONLINE_STATUS can change from 0-2 (Offline, partially online, online) and will change between them periodically.  The other AttributeIDs' AnalogValues may also change back and forth depending on other conditions.

Desired Output

Room Name          AttributeID     AnalogValue     LogTimeStamp
Conference Room    ONLINE_STATUS   0               2016-06-11 21:21:25:123
Gymnasium          ONLINE_STATUS   1               2016-07-21 20:23:45:456
Boardroom          ONLINE_STATUS   2               2017-02-17 05:15:37:951
Great Hall         ONLINE_STATUS   0               2016-10-23 07:28:54:753
...


texts: Postgres long running query not blocking table operations

texts:
We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to our view-data can take a LONG time (days sometimes). This isn't actually a problem for us, but the 3rd party script occasionally performs table operations (like drop) when it's rebuilding itself. Unfortunately those operations get blocked when we have queries running. Right now, due to the size of the data, we're using a postgres cursor (using the ruby sequel gem to assist with this) so we can fit a fixed chunked of data into memory. It seems that CURSORS though, have a sort of transaction running while iterating through the cursor.

I recognize that the best way to do this is take the results of a query and place them somewhere else as quickly as possible and then scan them to import that data and I know I could do that with temporary tables or other means, however I'm wondering if there's a way that a query can buffer its results (say, to a file on disk) without having to resort to managing temporary tables, such that it no longer locks schema operations?

For instance, I found there's a "single row mode" that streams query results, but I'm not entirely sure how postgres does that under the hood and if that would ultimately have the same effect on concurrently running schema operations.

Can someone enlighten me as to how "streaming" works and if this would solve my problem? Or do I have no other choice but to create a temporary table?


texts: How to Upgrade Redis Database from 5.x to 6.x

texts: This instance was running Redis version 5.0.8 open source. The Redis packages have been updated to the current 6.0.3 and now the server reports Loading RDB produced by version 5.0.8 on startup. What is the proper method for upgrading the RDB to the version of the server? I have checked the admin documentation and there's nothing mentioned. Also, I checked the 6.0 release notes and nothing there either.


texts: SQL Server: migrate one table to discrete DB server

texts:
SQL Server 2012 Standard

We have one table that is highly transactional (tons of inserts, no updates), that contains a BLOB object of 5kb-300kb per row. The whole table is about 31GB in size (table and indexes), and has 450K rows. There is a primary key (it is a clustered table), and there is a record creation timestamp col.

We want to move table to its own db server. We have already updated the application to reference this table via its own ADO connection string. Yes, we can take the app offline during a maint window to do this.

What is the best strategy to migrate a single table to another DB server?

Options we have considered:
 - Log shipping , but this replicates whole db... if we could log ship one table, we would do it for sure, we like log shipping
 -  Transactional replication... we do NOT like transactional replication because it is such a monster.

One thought is to bring the app down briefly, and do a tsql command to

insert into <new db server.db.table> select from <source db server and table>

Can that work? Can we do a tsql command across discrete db servers?
What other method we should consider?


texts: Splitting data into two tables, in one go

texts:
I am using SQL Server 2008 and have a problem which I don't know how to solve without using many temporary tables and an unreliable join.

Table 1 contains 6 columns of data, which is then split into two tables. Col1 to Col3 goes into Table 2, and Col4 to Col6 goes into Table 3. Getting the data into Table 2 and Table 3 is the easy part. However T2ID in Table 3 is a foreign key to the ID in Table 2.

Performance is key so I don't want to use variables and/or iterate through the data row by row, ideally I just want one insert which does the lot.

I've tried using a Link Table but the data in Table 2 and Table 3 is not unique which makes joining unreliable.

Any suggestions?

Create Table T1 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10),
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

Create Table T2 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10)
)

Create Table T3 (
  ID INT IDENTITY(1,1),
  T2ID INT,
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

I can't change T2 or T3. T1 is not a staging Table, and I can't alter that either. There are other packages also writing to T1, T2, and T3. Although it is possible to schedule them at different times should I need to.


texts: Error 2105 on Northwind derived Access database when trying to opening a blank form after changing the table source

texts:
I have an Access database which was built from the Northwind template, which is referenced here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases
I am new to Access and VBA.
The screenshots I show are from the Northwind Access template, not the database I am working on.
From what I can tell, the Home form's New Customer Order button uses an embedded macro with "1=0" (like, FALSE) in the where condition to retrieve zero rows from the Order Details form data and this allows for data entry. You can see in these settings how this is accomplished:

This is the full query used in "Record Source":
SELECT Orders.*, [Orders Status].[Status Name], [Customers Extended].[E-mail Address] FROM [Customers Extended] INNER JOIN ([Orders Status] INNER JOIN Orders ON [Orders Status].[Status ID]=Orders.[Status ID]) ON [Customers Extended].ID=Orders.[Customer ID];

Notice "Data Entry" is set to "No". More commonly, for data entry forms, this would be set to "Yes", but it seems "1=0" in the previous macro works to open a blank form.
If I open the Order Details form without using the New Customer Order button, the form will open fixed on the first order in the table, where I cannot change any of the values. This will also happen if I delete the "Where Condition" or set it to something like "1=1", then click the New Customer Order button.
I can click on the New (blank) record button at the bottom to get a blank form and enter new data.

In the current database, when I click on the New Customer Order button, I will get error 2105 ("You can't go to the specified record."), unless I remove the "1=0" condition. If I click "debug" on the error dialog box, I will be taken into the VBA editor for the Order Details form. Within the SetFormState procedure, the line, If fChangeFocus Then Me.Customer_ID.SetFocus, is highlighted as the point of failure. (fChangeFocus is True.) I can troubleshoot several things, but trying to set the focus to the Customer_ID field will always cause a 2105 error if the macro uses the "1=0" condition.
I cannot find a visible difference between the database I am using and the original template. I believe the difference must be in the tables the data is being pulled from.
I would like to find a solution, not necessarily using the "1=0" clause, that will allow the effects of clicking the button to be similar to the template and preserve the general formatting.
The current live database is working as intended, but after copying the tables to a new test database, the error occurs. This is with both computers and users who have tried this, and in different ways: Linking to SQL Server, or simply importing the tables into the test Access database copy. The live database uses links to tables in other Access database files on the server.
I am interested in knowing if others have seen this before and know how to resolve it.
I'll provide extra details if I can, but cannot share the data.
Here is the macro used when setting focus to the Customer_ID field:

I think that the issue on setting the focus is a symptom, rather than a cause; but I could be wrong.
Update:
I've realized that the New (blank) record button is grayed out in the working database. This could be an important clue about why opening on a new record isn't working. I've also removed the "1=0" and added the "GoToRecord" action to the New Customer Order "On Click" macro with "Record" set to "New" and other fields blank. This results in the 2105 error, so I think I'm on to something. I have changed the "Data Entry" option of the Order Details form to "Yes".


texts:
I'm trying to replicate the SQL required to create a user with privileges, if I use HeidiSQL GUI interface to create a USER and set the privileges it works, then I delete the user from the User manager in HeidiSQL and copy SQL from echoed response area at the bottom of HeidiSQL:
CREATE USER 'simon'@'localhost' IDENTIFIED BY 'password';

Just the above, nothing more, but it fails with a dialog containing:
SQL Error (1396): Operation CREATE USER failed for 'simon'@'localhost'


texts: In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update stats.


texts:
We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table with sample = 100%.
I've read that incremental statistics is not supported on AlwaysOn readable secondary databases.
So is it how SQL Server supposed to react on this situation?

We cannot allow this to happen, so our temporary solution is to remove those _WA_Sys_... statistics that SQL Server tries to update, then some of those statistics are still recreated by SQL Server but with a lot smaller sample rate.

Is anyone familiar with this issue and what can be done to prevent this kind of behavior?


texts: listing the permissions for a database object - and take into consideration it can be a synonym

texts:
I have a script that gives me all the permissions assigned to a database object:

-------------------------------------------------
-- permissions to database object
-- marcelo miorelli v. 20170401
-------------------------------------------------

declare @dbname sysname  = 'mydatabase'
       ,@ObjName sysname = 'thetable1'

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT

     /*Use QUOTENAME to correctly escape any special characters*/
    SET @sql = N'USE '+ QUOTENAME(@dbname) + N'

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

;WITH
RADHARANI AS (

                SELECT
                    dp.NAME AS principal_name
                        ,dp.type_desc AS principal_type_desc
                        ,o.NAME AS object_name
                        ,o.type_desc
                        ,p.permission_name
                        ,p.state_desc AS permission_state_desc
                    FROM sys.all_objects  o

              INNER JOIN sys.database_permissions  p
                         ON o.OBJECT_ID=p.major_id

         LEFT OUTER JOIN sys.database_principals  dp
                         ON p.grantee_principal_id = dp.principal_id

                   WHERE 1=1
                     AND O.OBJECT_ID > 0
                     AND O.TYPE <> ''S''    -- no system
                     AND O.parent_object_id = 0 -- no constraints
                     AND o.NAME like @ObjName

               UNION ALL

                SELECT
                    dp.NAME AS principal_name
                        ,dp.type_desc AS principal_type_desc
                        ,o.NAME AS object_name
                        ,[type_desc] = ''User-Defined Table Type''
                        ,p.permission_name
                        ,p.state_desc AS permission_state_desc

                    FROM sys.table_types  o

              INNER JOIN sys.database_permissions  p
                      ON o.user_type_id=p.major_id

         LEFT OUTER JOIN sys.database_principals  dp
                      ON p.grantee_principal_id = dp.principal_id

                   WHERE o.NAME like @ObjName

)

SELECT * FROM RADHARANI
SELECT @retvalOUT = @@ROWCOUNT
'

SET @ParamDefinition = N'@retvalOUT int OUTPUT,
                         @ObjName sysname';

BEGIN TRY
EXEC sp_executesql @SQL,
                   @ParamDefinition,
                   @retvalOUT= @retval OUTPUT,
                   @ObjName  = @ObjName;

IF ( @retval = 0 )
    BEGIN

        PRINT '------------------------------'
        PRINT 'The Object ' + @ObjName +
              ' does not have any permissions assigned to it.'
        PRINT '------------------------------'

    END--IF

This worked so fine, and I loved it, however, today I got a developer telling me about some problems, not being able to update a table.

this is not working:

UPDATE tbl_ORCAStatusChanges
SET messageStatus        = 1
WHERE     applicationID    = @applicationID
    AND messageStatus    = 0

But when  have a look at the permissions for the table tbl_ORCAStatusChanges
all seems to be fine because the user in question has the update permission:

I see it is not a table within my database, but a synonym.

this is how I found out at what other object this synonym is pointing to:

use Mydatabase
go

SELECT
    the_schema= SCHEMA_NAME(s.schema_id)
   ,the_object=s.name
   ,the_type =s.type_desc
   ,s.base_object_name,
  the_DB_ID= DB_ID(PARSENAME(base_object_name,3)),
  the_DB_name=DB_NAME(DB_ID(PARSENAME(base_object_name,3)))
FROM sys.synonyms s
WHERE 1=1
  AND s.name LIKE 'tbl_ORCAStatusChanges'

to which I get:

How do I include the synonyms in my script?


texts: Is it *always* bad to have long-running transactions, or is this case OK?

texts:
I do this:
BEGIN;
TRUNCATE TABLE test RESTART IDENTITY;
-- Tons of INSERTS to the test table here, taking minutes or many seconds each time.
COMMIT;

This is nothing fundamentally wrong with this, is it? Even though the transaction takes a long time before it finally commits, in this case it's simply necessary for the whole operation be "atomic" and not make other scripts see an empty or half-populated test table, right?
The other queries will still continue to work while it's taking its sweet time to finish, right? This is where my understanding of transactions falls apart. It seems to me that it's guaranteed that I will be able to SELECT from the table while this transaction block is running, but is it also possible to INSERT and DELETE and UPDATE?
My brain cannot comprehend how exactly the database would handle this. Is this what a deadlock is? Or will they continue INSERTing and UPDATEing and DELETING from a "ghost table" and all changes are lost when the long-running transaction finally COMMITs? That doesn't sound right. I assume that it would refuse any "changing" operation to the table while that transaction is running, correct?
Yes, I actually did the manual again today on transactions and deadlocks. Some people are able to read something once and understand it perfectly, while others (like me) can spend many years and repeatedly convince themselves that they have understood something, only to see "nonsensical" errors logged and Bad Things happening to their live production applications due to misunderstandings of how complicated things like this really work.


texts: ORA-00932 on subquery join from subquery join

texts:
The only guess I have is that perhaps the size of the subquery is considered a CLOB?  I have no null values for the fields selected AFAIK.

SELECT DISTINCT
    kbq_ent.qid AS kbq_ent_qid,
    kbq_ent.category AS kbq_ent_category,
    kbq_ent.consequence AS kbq_ent_consequence,
    kbq_ent.diagnosis AS kbq_ent_diagnosis,
    kbq_ent.last_service_modify_datetime AS kbq_ent_last_servic_1,
    kbq_ent.patchable AS kbq_ent_patchable,
    kbq_ent.pci_flag AS kbq_ent_pci_flag,
    kbq_ent.published_datetime AS kbq_ent_published_d_2,
    kbq_ent.severity_level AS kbq_ent_severity_le_3,
    kbq_ent.solution AS kbq_ent_solution,
    kbq_ent.title AS kbq_ent_title,
    kbq_ent.vuln_type AS kbq_ent_vuln_type,
    kbq_ent.discovery_remote AS kbq_ent_discovery_r_4,
    kbq_ent.cvss_base AS kbq_ent_cvss_base,
    kbq_ent.cvss_temporal AS kbq_ent_cvss_tempor_5,
    kbq_ent.last_customization_user_login AS kbq_ent_last_custom_6,kbq_ent.last_customization_datetime AS kbq_ent_last_custom_7
FROM kbq_ent JOIN (SELECT vuln_eid AS vuln_eid, qid AS qid, anon_2.rel_eid AS anon_2_rel_eid
FROM (
        SELECT entity_vuln.entity_id AS vuln_eid,
            entity_vuln.qid AS qid
        FROM entity_vuln
    ) JOIN (
        SELECT entity_responsible_org.entity_id AS rel_eid
        FROM entity_responsible_org
        WHERE entity_responsible_org.org_id = :org_id_1
    ) anon_2 ON vuln_eid = anon_2.rel_eid) anon_1 ON kbq_ent.qid = anon_1.qid

kbq_ent.solution AS kbq_ent_solution is a CLOB.


texts: Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

texts:
I tried to compute the audit log table usage using the following steps:

Truncate all the audit log table data (i.e. AUD$)
Perform test case that inserts and update data, also delete statement but it wont delete anything.
Measure the byte used on the dba segment.

select tablespace_name,bytes from dba_segments
where tablespace_name ='SYSTEM'
and SEGMENT_NAME = 'AUD$';

Recompute of statistical data.
Retrieve the byte used in the statistical data.

select NUM_ROWS * AVG_ROW_LEN as BYTE_USED
from DBA_TABLES where TABLE_NAME = 'AUD$';

The value I get from #3 is more than twice that of #5, Why is it allocating too much?


texts: SQL Server instance using only a few hundred MB. Well below the minimum server memory setting

texts:
I've got two SQL Server 2012 instances running on a server with 32GB of RAM and running little else.

One instance is behaving normally. Its working set memory is running close to its commit which is also close to the maximum server memory setting in SQL Server.

The other instance (highlighted in the image) is not behaving as I would expect. No matter how I configure its minimum and maximum memory settings, I cannot get its working set memory over a few hundred MB, even though there is plenty of available RAM on the server.

This is having a very detrimental effect on its performance especially under heavy load. The database is well used and when this instance was working normally* it would quickly fill up to the maximum server memory setting of 20GB.

The server has been restarted. The instances have been restarted. I've tried changing to various min/max settings and stopping the instance which is working fine - just to check it's not interfering somehow. The problem remains the same, while one instance seems to respond normally to the minimum and maximum memory settings, the other does not.

* I have no idea what's changed otherwise I'd change it back.

Both OS and SQL Server are 64 bit.
The contents of sys.configurations is identical on both instances.
The databases on the 'low' server total in the 10s of GBs.
I'm confident enough load is being put on the server that it should like to use more memory. In the past it used to fill its ram allocation quite quickly.
I've done some large queries on tables which should have pulled data into memory.
This is exclusively a database server, nothing else is running on it and it's running noticeably slower since this problem began.
sys.dm_os_sys_memory and perfmon agree with resource monitor.
Target Server Memory (KB) = 10240008; Total Server Memory (KB) = 5237744


texts:
Let's say I have Table A: BookingsPerPerson

Person_Id    ArrivalDate    DepartureDate
123456       2012-01-01     2012-01-04
213415       2012-01-02     2012-01-07

What I need to achieve with a view is the following:

Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
123456       2012-01-01     2012-01-04       1         1         1         1
213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

The system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. Any ideas would be very much appreciated.


texts: Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

texts:
I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ.

For context, there was an issue where the existing SQL Server backups had to be moved to new share, and so the history in MSDB and the current location of the new backups would not match what exists now.

When running the default job that MaintenanceSolution.sql creates, "DatabaseBackup - USER_DATABASES - FULL" (with minor modifications to the parameters it runs on) I see the job now fails because it appears that deleting the files it expects would exist:

EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\UNC\Path\Server\Database...  Process Exit Code 1.  The step failed.

After the first error of this type, the entire job comes to a halt.

In the FAQ https://ola.hallengren.com/frequently-asked-questions.html , I found the following paragraph which addresses the issue I believe I am having:

  Why does my job stop after the first error?

  This issue is happening because you’re using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then the job will continue after an error.

  You can use the MaintenanceSolution.sql script to create the jobs.

I double checked (because I thought this was the default that the solution configured) and based on what I see in SSMS it is (Type: Operating System (CmdExec)) and found that this was the case.

I also verified that the job is also using the -b option:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\UNC\Path\', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = 750 , @CheckSum = 'Y', @LogToTable = 'Y'" -b

I was quickly running through the stored procedure and found that it simply assigns any error from the xp_delete command to a variable and raises it as an error (completely bog standard sql), so is there anything missing to make this work without modifying his solution?

Thanks!


texts: Restored DB to Dev and test environments, now user can not login

texts: I took a backup of our production database and restored it to both the test and development environments last night, but this morning a single user is not able to login to the test/dev DBs. I can go into SSMS and connect to SQL Server with the user but when I try to connect to the specific databases I am given a 4064 login failed error. The credentials should be correct as I can turn around and login to production and run queries with no issues. I have compared both permissions sets and did not see any difference between prod and dev/test. They both have "connect" permission to the database schema and they are both part of the "db_reader" and "db_executor" groups. What might I be over looking in the test/dev system that is preventing this user from connected to the Database?


texts: SQL Server Linked Server Best Practices - Step by Step

texts:
Hello and thanks for stopping by.  I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way.  MS provides complete descriptions of the datatype and descriptions of all of the various parameters for sp_addlinkedserver, sp_addlinkedsrvlogin and sp_serveroption but no guidance on HOW to align the various options as Best Practices for a given situation.

I have examples from the other DBA's who simply used the 'sa' password but my research indicates I should be using bespoke logins tailored to their Linked Server use.  The Problem is I'm so far unable to find the right combination and sequence (order of ops) to correctly create all of the parts and pieces resulting in a Linked Server that allows limited communication between two servers.

Goal: Create a Linked Server between a Source server that will allow a job step from the source server to check certain conditions and if TRUE, invoke sp_start_job on Destination server. ...and nothing more.

On advice, I've created two SQL Auth Logins of the same name/pw on both Source and Destination, both with limited 'public' permissions.

I've created Linked Servers attempting to map the local login to the remote login (thinking if I got that far, I'd carefully tinker with the permissions of the Destination login to find the permission to allow it to exec sp_start_job).

But so far, my only reward has been a series of failure notices of various types.

There are a TON of online documents explaining what each various proc/param does but I'm having a difficult time finding some sort of over-view explaining how combinations of procs/params lead to different desired outcomes.

I'm hoping for some useful advice, reference to some 'yet to be discovered' tutorial or maybe even a Step by Step instruction on how to achieve my goal and develop a little self respect.  (so far, this task has done nothing but bruise my ego!)

Thank you for your time.


texts: Find first non-matching character between two strings

texts:
Posting this with solution provided.

The use case could be framed in a number of ways:

Return all characters that match at the start of two strings, until they do not match
How many characters match between two strings before one character doesn't match?
What is the first character that doesn't match between two strings?
etc.

For example:

If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.

For the question in the title, the first non-matching character is number 9.


texts: Does avg_query for PgBouncer include update and inserts?

texts:
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:

avg_query
    Average query duration in microseconds.

includes the average duration for INSERTs and UPDATEs as well as SELECTs.

Thanks very much!


texts: Best embed/reference/field strategy for many inserts (mongo)

texts:
I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size.

My models:

User

Match
 -ReferenceMany (User)
 -ReferenceMany (Score)

Score
 -ReferenceOne (Match)
 -ReferenceOne (User)

It takes 2 users to start a match. Say we get 10.000 users, and all play one match each day against another user, we get 5000 games a day.

Each match has three turns, which gives 6 scores, so for 5000 games we get 30,000 scores. These scores can be inserted simultaneously (in each game), so I have to make sure one user doesn't overwrite another users score. I believe I've solved that by having the scores in their own collection, and embedding them on the match, like so:

$score = new Score();

$score->setUser($user);
$score->setScore($playerScore);
$score->setGame($game);
$score->setMatch($match);

// Save score.
$dm->persist($score);

// Add score to match.
$match->addScores($score);

My issue with this approach though is that it takes 6 queries just to insert a score (there are some validating queries before the above code). With 30.000 scores a day, that's a whole lotta queries.

My initial thought was to just add the scores to the match as an array, but what will happen the if two users submit at the exakt same time, updating the same match?

I'd be really interested in hearing some opinions on this. Let me know if I need to clarify anything. I'm using Symfony2 with Doctrine ODM if that helps.


texts: PostgreSQL full text search words delimiter

texts: Please help me to understand how to change words delimiter when I use gin index. The problem is that "," ";" and " " signs are words delimiters but the "." isn't. So in phrase "Some 11.22.33.44 IP" I am able to find "Some" "11.22.33.44" "IP" words. When I am looking for "33" - unable to find it. Many thanks for the help.


texts: How to check mysql password change and other events logs

texts:
Recently someone change my phpmyadmin password and drop database

I think it was sql injecting.

Now I want to find information about this like when, how and who change my password

Please tell me where I can find my mysql v 5.5 logs (Windows Server 2012)

Thanks


texts: Create SQL Server user with SQL Server Authentification

texts:
I am trying to login to a SQL Server 2014 from another service (AWS Schema Conversion Tool). In the AWS tool I need to input 'User name' and 'Password'. Currently I am using windows authentication for connection to a MS SQL DB. My user name as per SSMS is 'Server\user'. I have tried logging into the DB from the AWS tool using user as 'Server\user' and 'user' with my windows password but keep getting a 'Incorrect username or password alert'.

To login from the AWS tool I would like to create a dummy user who has admin privileges and SQL Server Authentication. In SSMS have tried

Server->Databases->DB->Security->New user.

I created a new dummy windows admin user the tried to create 'SQL user with login' and got the error 'Create failed for user 'dummy''. ''dummy' is not a valid login or you do not have permission'. I am logged in with admin rights and 'dummy' definitely exists as a windows user.

Any ideas on how I can create a new user with SQL Server Authentication?


texts: How can I perform this database backup/sanitization task?

texts:
I have a 3-node MariaDB/Galera cluster.  I need to do two things:

Back up the database regularly (multiple gigs in size) for a normal, in-case-everything-crashes-I-can-restore-from-this backup.
Have a sanitized version in which private data is obfuscated and key values are changed, for the purpose of distributing to developers.  (The idea is that every day, they can grab a fresh "devel snapshot" that is a content- and structural- facsimile of the real thing, but that in which any potentially sensitive data is sanitized, and devel-centric settings are changed.

I know how to do a mysqldump, but I'm afraid this will lock the db.

I know how to write the queries to obfuscate the data, but obviously I can't do this on the live db.  (I'm assuming that I need to import this into a different database... perhaps on a separate machine not in the cluster?)

I need this to be scripted (which I'm not scared of doing, I'm just not sure where to start.)

Can someone point me in the right direction?


texts: Can TRUNCATE TABLE be overloaded on SQL Server?

texts:
I know it may look like a weird question...

I have a 3rd party db and services, and I want to monitor it by DML triggers on INSERT, UPDATE and DELETE of each table.

But alas, I have a problem when TRUNCATE TABLE is called, since it's not activating the DELETE triggers.

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows
TRUNCATE TABLE X; --truncate the empty table

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

I'll be happy if there are any other proposals too.

Is something like this possible?

EDIT
I don't want to disable the truncate I just want to monitor the data.

It is 3rd party DB, and it been used by services that I can't have control on, so I can't prevent the truncate.

I'm just trying to monitor changes and currently can't track the deletion of records caused by truncate command.

Thanks!


texts:
I'm trying to increase the performance of a query and I'm out of ideas:

The query does this: gets a 'Process' with specific priorities on selection namely:

Picks up a process that has been waiting for over 15 minutes, AND has either no 'sleep until' value set, or the 'sleep until' is less than the time NOW AND ProcessState = 1.

Possible values for ProcessState are 1,2,3,4

The Query I'm trying to optimize is below: it runs very frequently:

SELECT TOP (1) [t0].[ProcessID],
 [t0].[MaterialID],
  [t0].[VideoVersionID],
   [t0].[AudioVersionID],
    [t0].[TXVersionID],
     [t0].[XMLVersionID],
      [t0].[SubtitleVersionID],
       [t0].[Progress],
        [t0].[ProcessStatusDescription],
         [t0].[WorkflowProcessState],
          [t0].[WorkflowProcessSubState],
           [t0].[ProcessState],
            [t0].[ProcessStateDateLastModified],
             [t0].[DateCreated],
              [t0].[DateLastChecked],
               [t0].[SleepUntil],
                [t0].[LongRunningProcessID]
FROM [dbo].[WF_Process] AS [t0]
WHERE ([t0].[ProcessStateDateLastModified] <= '2015-06-18 09:48:31.597')
AND (([t0].[SleepUntil] IS NULL) OR ([t0].[SleepUntil] <= '2015-06-18 10:03:31.597'))
AND ([t0].[ProcessState] = 1)
ORDER BY [t0].[ProcessStateDateLastModified]

The only things that change are the two date time fields, which operate a sliding window from DateTime.Now(). First paramter DateTime.Now()- 15 minutes, second date time paramater is DateTime.Now().

Execution plan here: https://gist.github.com/anonymous/fd56ead25d726d247518
Or execution plan diagram here:

The table looks like this:

CREATE TABLE [dbo].[WF_Process](
    [ProcessID] [bigint] IDENTITY(1,1) NOT NULL,
    [MaterialID] [bigint] NOT NULL,
    [Progress] [decimal](18, 2) NULL,
    [ProcessStatusDescription] [varchar](900) NOT NULL,
    [WorkflowProcessState] [int] NOT NULL,
    [WorkflowProcessSubState] [int] NOT NULL,
    [ProcessState] [int] NOT NULL,
    [ProcessStateDateLastModified] [datetime] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateLastChecked] [datetime] NULL,
    [SleepUntil] [datetime] NULL,
    [LongRunningProcessID] [varchar](65) NULL,
 CONSTRAINT [PK_Process] PRIMARY KEY CLUSTERED
(
    [ProcessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[WF_Process] SET (LOCK_ESCALATION = DISABLE)
GO

ALTER TABLE [dbo].[WF_Process]  WITH CHECK ADD  CONSTRAINT [FK_Process_GB_Material] FOREIGN KEY([MaterialID])
REFERENCES [dbo].[GB_Material] ([MaterialID])
GO

ALTER TABLE [dbo].[WF_Process] CHECK CONSTRAINT [FK_Process_GB_Material]
GO

I have a combined index on all the columns which are within the query: ( and I have other indexes on the table too )

There are 187,851 rows in the database with a data space of 50MB. It has a total Index space of 483MB.

The Query IO Statistics look like this:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 27566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

The client statistics looks like this:

The DB server is meaty. DL360 gen8 ( 32 GB ram, 30+ cores etc ).

This query is very heavily used, and so I'm trying to optimize it as much as I can, any help/information to speed up this query would be appreciated. Anything over 500 ms isn't really acceptable.

EDIT:

Did as Zohar said and I now have [ProcessStateDateLastModified] as Key index column, and the other two columns [SleepUntil] and [ProcessState] as included columns. This reduced the number of logical rads ( expected ), but did not reduce the overall timings of the query:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Execution plan:

Client stats:


texts:
SHOW TABLES

shows table: foo

DESCRIBE foo

Gives me:

ERROR 1146 (42S02): Table 'foo' doesn't exist

At one point, table foo did exist, but it was dropped because it wasn't needed.

Seems the schema still thinks it's there. How do I fix that?


texts: MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

texts:
I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which initially is empty).

I have noticed that the previous max value partition is copied to a 2 new files (one for the previous week partition and one for the new max value partition), the second file is empty since it represents a partition for future not existing data.

Then i wondered why recopying data from previous max value partition to new partition file and then deleting the previous max value file, and creating a new empty max value partition file, it seems as a big waste of time.

I have to mention i deal with MyISAM tables, Additionally i have stumbled on this reported bug which describes very similar conditions.

Is there a workaround method to achieve the behavior i have suggested (renaming the partition of max value to a new partition name and replacing the max value constrain by a new current max value measurement, then creating a new max value empty partition?
Can you suggest other way to close a partition -> activate some command that will close (add max limitation on the previous max value partition) an open a new max value partition?


texts: Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance

texts:
I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and finally restore into 2012.

Fine, except we have no 2005 or 2008 instances available.

Are there any workarounds or other methods that might be worth trying?

For information, the databases only contain 15-20 tables and a few views, they appear very simple and the backups are only 100-200MB in size.