texts: How properly call stored procedure from EXEC(SQL)?

texts:
I have engine is SQL which uses EXEC(SQLString) to run stored procedures. I do that because I need dynamically determine which stored procedure to call and to prepare parameters for that procedure. And I need that logic to be present.

So is this a good way to call stored procedure or I have to use sp_executesql or there is another way?

I ask this question because I have two timings - 1st is to prepare SQLString and to call procedure and call another small procedure and 2nd is the execution of the called procedure itself. It works fine almost all the time - the timings are very close. But from time to time I got huge difference between them - the 2nd timing is the same, but 1st is several times bigger (for example 3ms vs 90ms). I suppose some compilation takes place, but this stored procedure is executed very often.

REMARK:

I call sp_ from EXEC(SQL), not from EXEC sp_.

Here is the code how inner SP is called:

BEGIN
    -- DECLARE results table (results from SP are get via select and insert into from execution)
    DECLARE @table AS TABLE (
        ErrorTxt NVARCHAR(MAX) NOT NULL,
        XMLOut XML NULL);

    -- Prepare SQL for calling SP
    SET @sSQL = 'EXEC dbo.sproc_Method_0000129' +
        CASE
            WHEN (@iRes = 0) THEN '9'
            WHEN (@IsOut = 1) THEN '8'
        END + '_' +
        RIGHT('000' + CAST(@IDParam3 AS VARCHAR(3)), 3) + ' ' +
        '''' + REPLACE(CAST(@XMLIn AS VARCHAR(MAX)), '''', '''''') + ''', ' +
        ISNULL(CAST(@IDSrv AS VARCHAR(10)), 'NULL') + ', 0';

    -- EXEC SP and get results
    INSERT @table EXEC(@sSQL);
    SELECT TOP 1 @sErrorText = ErrorTxt, @XMLOutput = XMLOut FROM @table;

    -- Add log for timing
    EXEC dbo.sproc_AddTiming @XMLExtraParam OUT, 'SlowOperation', @dtOperation OUT;
END;


texts: Necessity of backing up tail log when restoring transaction log?

texts:
I have a question about whether or not it is necessary to back the tail log up when performing a restore of a database in Full Recovery mode. I've sprawled for a good while now, and have gotten conflicting reports.

Consider this scenario in SQL Server 2012:

Full database backup scheduled at 4:00 AM every morning.
Transaction log back ups every 15 minutes from 7:00 A.M. to 6:00 P.M.

With this backup plan in place, say we want to perform a deployment against this database after the final transaction log backup has kicked off at 6:00 P.M.. We then run various tests and fill the database with data that does not need to be retained after confirmation of a successful deployment.

The deployment is successful, all tests look to be in line, and we restore back to the 6:00 PM transaction log to rid of all the data just entered. We then run a final deployment against the database. At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

I am receiving conflicting reports and dated posts/articles.

Could anyone outline the pros/cons of not performing the tail log backup?

Thanks!


texts: Alternating usernames while connecting to mysql database

texts:
My server has a max_connections value of 400 and a max_user_connections of 250. Since I am always connecting with the same user name, my effective max_connections is 250 instead of 400. To solve this, I though to connect with one username 50% of the time, and another username 50% of the time:

if(mt_rand(1,2) == 1){
  $con = mysqli_connect(DB_SERVER, DB_USER_1, DB_PASS) or die(mysql_error());
}
else{
  $con = mysqli_connect(DB_SERVER, DB_USER_2, DB_PASS) or die(mysql_error());
}

My question is, are there any downsides to doing this relating to the fact that the same person might alternate back and forth between different users? Or is this not a problem as the previous connection is "forgotten" and when the user refreshes we are starting a new connection and there is no relation to what user he/she connecting with last page refresh.

In general, is there a better way to deal with this?


texts:
I have emp table (empno, ename, job, mgr, hire_date, sal, deptno)
with no index at all.
 If i run select * from emp  i get the rows ordered by empno.
Now i create a clustered index like this:

Create clustered index name on emp(ename)
If i run select * again, the rows are now ordered by ename.
Now if i drop the index. drop index name on emp I would expect to see the rows ordered back by empno, like before creating the index.
Instead I still see the rows ordered by ename, as if the index were still there.
Is there a way to see the rows ordered back by empno as before without using "order by empno?".
I tried to log off and connect again to make a refresh, but did not work.
The only way was to drop and recreate the table.
Maybe is there a parameter in the table definition that is changed when the  index was created, and it is not updated when the index is deleted?

It seems to me that there is a default method for displaying the data before the index, which is not preserved / restored when the index is deleted


texts: Replication broke table compression and partition though I can still see the ndf files having data sql server

texts:
I recently did a backup of a table,compressed it and partition it.Then I did replication to it using the filter option to get latest data.Now I can only see the latest records and not the old records but the ndf files are there.
Is there a way to recover these records?I mean tie back the partition to this table so that I can see the data?


texts: Easily give role in one database access to tables in another database on the same server

texts:
I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y)

In database X I a have a lot of users defined, but their access rights come down to a manageable handful of roles. I'd like to take one of these roles and use it to grant select access to a few tables in database Y. I really want to avoid creating a new role in database Y and re-adding all the users, not just because of the busy-work setting up that security info, but also because of needing to keep these roles in sync over time, as users come and go. Really, the role that already exists in database X already does a great job of logically defining the access group I want in database Y from a business standpoint, and I want to keep it that way.

How can I grant access in Y for this role from X?


texts:
I used Ola's script to backup my database (SQL Server 2017) and now I'm trying to restore it using Brent's sp_DatabaseRestore. Currently trying to do a Full restore only. Here is my code, written according to the web page:

EXEC dbo.sp_DatabaseRestore
  @Database = 'LOTR_recovery',
  @BackupPathFull = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\OlaBackup\MY-PC\LOTR\FULL\',
  @ContinueLogs = 0,
  @RunRecovery = 1;

I put the SP on the destination database as well as Ola's scripts. Apart from these two, the database is empty. I am getting the following error:

 Getting default data drive for @MoveDataDrive
 Getting default log drive for @MoveLogDrive
 Setting default data drive for @MoveFilestreamDrive
 @ExistingDBAction 0 so do nothing
 @ContinueLogs set to 0
 Msg 50000, Level 16, State 1, Procedure dbo.CommandExecute, Line 98 [Batch Start Line 0]
 The value for the parameter @Command is not supported.

 Date and time: 2019-01-02 13:18:32
 Command: RESTORE DATABASE [LOTR_recovery] WITH RECOVERY

 Msg 3153, Level 16, State 2, Line 1
 The database is already fully recovered.
 Msg 3013, Level 16, State 1, Line 1
 RESTORE DATABASE is terminating abnormally.
 Outcome: Failed
 Duration: 00:00:00
 Date and time: 2019-01-02 13:18:32

I tried to search for msg 50000 and @command but without success.

In addition, must sp_DatabaseRestore be created on the destination server I want to restore on, in order to work?


texts: Query executes fast, but keeps the table locked for some time

texts:
Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table.

It creates a table with a foreign key constraint and inserts 8000 random values, with the foreign key always NULL.

The 8000 rows are inserted very quickly, and it is possible to query them very quickly.

But it's impossible to drop the table for a few seconds.

PRAGMA foreign_keys = 0;

DROP TABLE IF EXISTS Drawings;

CREATE TABLE Drawings (
    PartNumber       TEXT,
    Description      TEXT NOT NULL,
    ParentPartNumber TEXT,
    FOREIGN KEY ( ParentPartNumber ) REFERENCES Drawings ( PartNumber )
);

CREATE UNIQUE INDEX PartNumber_Idx ON Drawings (
    PartNumber
);

INSERT INTO Drawings (
    PartNumber,
    Description
)
  WITH RECURSIVE
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 8000)
  SELECT x, random() FROM cte;

PRAGMA foreign_keys = 1;

SELECT *
  FROM Drawings;

--DROP TABLE IF EXISTS Drawings;

EDIT

I just found out that disabling the foreign key check while dropping the table speeds up the operation:

PRAGMA foreign_keys = 0;
DROP TABLE IF EXISTS Drawings;
PRAGMA foreign_keys = 1;

The question is still open: why dropping a table (or performing other operations that I haven't identified yet) cannot be done immediately after populating the table above, but it can be done after leaving the database alone for a few seconds or after disabling the foreign key check?


texts: Moving Partitioning Files To Another Disk Drive In SQL Server

texts:
There is a production table which was partitioned by someone in our company. Some partitioning files was wrongly located to different disk drive.

I have to move those files to the disk where they have to be. Those partitioned files for 2020 and 2021 are on production. How can I move those files to the main disk drive without stopping the database or services?


texts:
Starting with SQL Server 2005 and all later versions, Enterprise Edition includes a feature called "Fast Recovery".

a) Does fast recovery use multiple OS threads to perform "REDO" operation?

b) Does SQL Server write to the transaction log in parallel spawning multiple threads or is the log written sequentially so only one thread is used for writing to the transaction log?


texts: Is it better to use FREETEXT or CONTAINS Full Text Searches when searching by single characters

texts:
I am trying to come up with the most efficient way to search for records in a collection of about 130,000 total records.  The long, short of it is we have about 130,000 Active Directory records.  I am building a site that looks up users in AD.  I would like to implement TypeAhead functionality on a Search box.  I could query AD directly in code, but am thinking about dumping the data into SQL Server and querying that instead.  TypeAhead does a search as you type characters in the textbox.  Is it efficient to search a table of 130,000 records by single characters?  I am trying to determine if the searching should be done by the SQL Server engine or on the site?  I am thinking SQL Server.  If true, is it better to use FREETEXT or CONTAINS full text searches?

Curious what the community thinks about this?  Or am I way off?


texts: Select from linked server to local server causing waits on both sides

texts:
The queries in question are here: http://pastie.org/4833351
Run individually, each of these queries runs through (albeit slow).
Run as a group, it just sits there, showing either OLEDB waits on the local box or in a SUSPENDED state on the linked server (wait type: ASYNC_NETWORK_IO).
The query finished (after 44 mins) with the following errors:

OLE DB provider "SQLNCLI10" for linked server "[MyServer]" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI10" for linked server "MyServer" returned message "Query timeout expired".
Msg 7330, Level 16, State 2, Line 127 Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "MyServer".

Can anyone here point me in the direction of how I can both diagnose and fix this?


texts: What's a good way to add a PRIMARY KEY to a large InnoDB table?

texts:
I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine.  This table has a foreign key that links to another table with around 3 million rows.

I want to add a primary key to the larger table, but am unsure of how best to go about this.  There's no existing column (or set of columns) I can use for this, so how should I go about this?

I have seen some references to an internal InnoDB record ID, but have not found if this is accessible.

Otherwise, perhaps I will need to dump and reload the data, adding the id in manually.

Thanks.


texts:
In SQL Server, I have a varchar column with value such as:

 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22

How do I convert this column to a datetime column with real datetimes?
If this is not possible, how do I just create a new column and copy this values into it but as datetime values?


texts: Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

texts:
I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.

I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual core and 4GB memory and 40GB SSD.

The log errors are:

120413 23:57:15 [Note] Plugin 'FEDERATED' is disabled.
120413 23:57:15 [Warning] option 'innodb-autoextend-increment': unsigned value 2000 adjusted to 1000
120413 23:57:15  InnoDB: Initializing buffer pool, size = 2.9G
120413 23:57:15  InnoDB: Completed initialization of buffer pool
120413 23:57:16  InnoDB: Started; log sequence number 0 44234
120413 23:57:16 [Note] Event Scheduler: Loaded 0 events
120413 23:57:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
120414  0:00:25 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=e2-relay-bin' to avoid this problem.
120414  0:00:25 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'.
120414  0:58:37 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87846901'.
120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

My.cnf is as below.

default-storage-engine=innodb
default-table-type=innodb
key_buffer              = 384M
max_allowed_packet      = 64M
thread_stack            = 256K
thread_cache_size       = 16
max_heap_table_size     = 64M
myisam_sort_buffer_size = 64M
join_buffer_size        = 8M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
sort_buffer_size        = 3M
tmp_table_size          = 64M
# Innodb changes
innodb_additional_mem_pool_size = 16M
innodb_autoextend_increment     = 2000
innodb_buffer_pool_size         = 3000M #As current Db is around 1.2G.
innodb_file_per_table
innodb_data_file_path           = ibdata1:512M;ibdata2:512M:autoextend
innodb_flush_log_at_trx_commit  = 2 #For more reliablity use 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 128M #Transaction Log up to 1/4 Buffer Pool
innodb_thread_concurrency       = 16
#innodb_force_recovery          = 2
#innodb_read_io_threads         = 8
#innodb_write_io_threads                = 8
innodb_lock_wait_timeout        = 50
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 400
table_cache            = 1024M
thread_concurrency     = 16

Any suggestions what I can change.


texts:
I need to store products in two forms, simple and variable.
Simple products have a name, desc and price.
Variable products have a name and description and each variation will have additional fields, variationName, price.
This is a sample schema:

So a product will contain its name and description in the products table and the price will go in the variation table.
Here's an example of a simple product:
Name, description, variationName, variationPrice
Product X, sample description, NULL, 3.99
And a variable product:
Name, description, variationName, variationPrice
T-Shirt, T-shirt description, Small, 2.99
The variations for T-Shirt would be things like Medium, Large etc each with its own price but same parent (T-Shirt).
The problem with this design I am having is keeping the price for simple products where a variation name isn't needed. How to store this without redundancy?


texts: Ample disk space with error: "1034 Incorrect key file for table 'table_name'; try to repair it"

texts:
I'm attempting to alter a 1.6 GB InnoDB table with the following query:

ALTER TABLE `table_name` ADD `field_id` int(11) unsigned DEFAULT NULL

The query fails with the following error:

1034 Incorrect key file for table 'table_name'; try to repair it

Nearly every thread on this topic (SO and elsewhere) suggests that this is due to the tmpdir running out of space.

While this seems to be the problem for many, this doesn't seem to be the case on our system.

tmpdir is /tmp:

/tmp is far from full:

I'm totally stuck.  Thoughts?


texts: Postgres develops server doesn't listen error spontaniously

texts:
I have been running Postgres 9.3 continuously for days on a windows 7 machine with no problems. This morning I came in to see that Postgres, and every application using my postgres database had crashed. I had been running an Java program in Eclipse that I have run many thousands of times in the past with no problems, the program makes new tables based on other tables in the database, my log suggests the program ran ok and produced its result as normal.

Now when I try and start the server from Pgadmin I get the server doesn't listen error message. How can I get the server running again, or if that's not possible recover my data? there are many important tables in the database and restoring them from the backup will be a real pain! (especially if this is going to happen again!).

Edit:

The log file says:

2015-12-09 03:22:44 GMT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

2015-12-09 03:22:44 GMT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

2015-12-09 03:23:08 GMT LOG:  received fast shutdown request
2015-12-09 03:23:08 GMT LOG:  aborting any active transactions
2015-12-09 03:23:08 GMT LOG:  autovacuum launcher shutting down
2015-12-09 03:23:08 GMT LOG:  shutting down
2015-12-09 03:23:09 GMT LOG:  database system is shut down

I tried stopping antivirus and disabling my firewall, neither helped, nor did updating postgres.

Edit 2:

I have found that the postgresql-x64-9.3 service is listed as "stopped" in task manager. I have tried going to the services menu and restarting it. I get the following message:

The postgresql-x64-9.3 - PostgresSQL Server 9.3 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.


texts: MySQL is using an Index not listed in possible_keys

texts:
In a very basic setup, I have a table with some fields:

firstname (VARCHAR)
lastname (VARCHAR)
country (INT)
email (VARCHAR)

I created in Index over 2 fields:

CREATE INDEX countr_email_idx ON person (country, email)

This is a simple example from school, so no assumptions on whether the index is of any use should be made.

Now when I query a simple Select over the email field, the MySQL documentation states that because it is not the left-most attribute of my index, it can't be used. This is also reflected in the empty possible_keys value when i run the query with EXPLAIN:

EXPLAIN SELECT passenger_id, email
FROM passengerdetails
WHERE email LIKE 'bob%'

which yields

id | select_type | table            | type  | possible_keys | key              | key_len | ref  | rows  | Extra
1  | SIMPLE      | passengerdetails | index | NULL          | countr_email_idx | 225     | NULL | 36370 | Using where; Using index

However, countr_email_idx is listed as the index that is used after all. Why does this happen?


texts:
We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're expecting a very large amount of data. We're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services.

My question is regarding the table structure for companies and people. A company can be a vendor, customer, contractor, prospect or financial institution.  A person can be a vendor, customer, contractor, employee or prospect.

In previous databases, I have have used a master table containing the common fields and secondary tables to hold the detail pertaining to the type of company or person.

For example:

CompanyMaster (ID, Name, Website, ParentID, etc)
CompanyVendor (CompanyID, CreditLimit, CreditTerms, etc)
CompanyContractor (CompanyID, RatePerHour, RatePerMile, etc)

Similar situation for Person's table.

With regards to performance and best practice, is it better to do it this way or split them into separate tables and not use a master table?

CompanyVendor     (ID, Name, Website, ParentID, CreditLimit, CreditTerms, etc)
CompanyContractor (ID, Name, Website, ParentID, RatePerHour, RatePerMile, etc)

I have a CTO telling me that they handled 3 million transactions per day using the multi-tenant database at his previous employer. He says it's all about the indexes and I know it's more than that.

We will be hiring an Azure Partner and most likely hire/contract a DBA. I have had experience with databases that had 100K companies and 300K people using the model above, but I thought due to potential size of the multi-tenant solution that it may be better to split them into smaller tables.


texts: Avoiding entitized characters when using FOR XML PATH for string concatenation

texts:
I have this query:

SELECT DISTINCT
    f1.CourseEventKey,
    STUFF
    (
        (
            SELECT
                '; ' + Title
            FROM
            (
                SELECT DISTINCT
                    ces.CourseEventKey,
                    f.Title
                FROM
                    CourseEventSchedule ces
                INNER JOIN Facility f
                    ON f.FacilityKey = ces.FacilityKey
                WHERE
                    ces.CourseEventKey IN
                    (
                        SELECT CourseEventKey
                        FROM @CourseEvents
                    )
            ) f2
            WHERE
                f2.CourseEventKey = f1.CourseEventKey
            FOR XML PATH('')
        )
    , 1, 2, '')
FROM
(
    SELECT DISTINCT
        ces.CourseEventKey,
        f.Title
    FROM
        CourseEventSchedule ces
    INNER JOIN Facility f
        ON f.FacilityKey = ces.FacilityKey
    WHERE
        ces.CourseEventKey IN
        (
            SELECT CourseEventKey
            FROM @CourseEvents
        )
) f1;

It produces this result set:

CourseEventKey Titles
-------------- ----------------------------------
29             Test Facility 1
30             Memphis Training Room
32             Drury Inn &amp; Suites Creve Coeur

The data is accurate, but I can't have FOR XML PATH('') because it escapes certain special characters.

To be clear, I'm using FOR XML PATH('') because it is possible for records with the same CourseEventKey to have multiple Facility titles associated with them.

How can I retain the data returned by this query without using FOR XML PATH('')?


texts:
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database up and working and forgot it.
Fast forward to now, I just did a backup up my whole data-dir using percona's xtrabackup and transferred it to a new server. Copy the backup into my data-dir and attempt to start mysql.  Fails with
Tablespace 1, name 'sys/sys_config', file './sys/sys_config.ibd' is missing!

mysqld: Can't create/write to file './performance_schema/cond_instances_2201.sdi' (OS errno 2 - No such file or directory)
mysqld: Can't create file './performance_schema/cond_instances_2201.sdi' (errno: 2 - No such file or directory)

I look at the the data-dir (/var/lib/mysql) and while I notice my main database folder, I don't see a sys or performance_schema folder. Curiously I do see a mysql folder but its full of files called slow_log and general_log.
My first thought is the backup failed so I checked the old server and it has the exact same thing! However, I do have a mysql.ibd on the old server. So I run show databases and it shows all 3 databases (sys, performance_schema, mysql). I try to go to sys/sysconfig and it complains about the table space but I am able to go into mysql database tables and perofrmance_schema tables.
Something is definitely up and I read people suggest doing an upgrade and mysql can self correct missing main tables (mysql_upgrade is deprecated so I must use mysqld and force it). I am scared to run update on my current server so I go back to the new server (the one I attempted to restore from backup)
I rename /var/lib/mysql/mysql to /var/lib/mysql/backup and execute sudo -u mysql mysqld --upgrade=FORCE --skip-grant-tables --skip-networking
and get in the error log
/usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4000
2020-12-13T23:41:31.040574Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-13T23:41:31.279802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-13T23:41:31.281181Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci' failed with error code = 1049, error message = 'System schema directory does not exist.'.
2020-12-13T23:41:31.281565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-12-13T23:41:31.281986Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-12-13T23:41:32.579767Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12)  Percona Server (GPL), Release '12', Revision '7ddfdfe'.

So I am not sure what to do, I would prefer to get my new server up and running instead of messing with my old server because it's currently live and being used.
I have innodb table per file
Should I expect to see a mysql folder inside my data-dir that looks to be slow_log and general_log backups? Will the sys,performance_schma,mysql databases have their own folder in the data-dir?
I wish there was a way to tell mysql to disregard the sys,mysql,performance_schema tables and re-create them, I thought that's what the upgrade option would do but it fails
Edit 12/23/2020
I think its problem that only shows up with the newest mysql. The backup was done on Percona Mysql 8.0.19-10  and the failed restore was attempted on Percona Mysql 8.0.21-12.  Normally I wouldn't think it was the difference in versions...
However, I just happen to upgrade the percona mysql to 8.0.22-13 on the the one that was previously 8.0.19-10 and the exact same issue occured. I believe the newer mysql version is more picky and fails to load if sys_config.ibd is missing where as 8.0.19 had no issues
Edit 12/24/2020
I uninstalled percona 8.0.21-12 on the target server and installed 8.0.19-10. Restored the backup with no issues (despite missing sys folder, etc). While the problem of missing a sys folder is still an issue it seems to only prevent startup with 8.0.2x


texts: Repeated login issue SQL Server 2008 R2 (SSIS)

texts:
I'm trying to figure out an issue I'm currently having with an SQL Server connection - there has been a few issues with timeouts on a SSIS package, where it has failed a login attempt - however as far as I am aware, the job should only connect once (Fwiw: The SSIS task is a simple datamart query - it returns results from a complex query into a single table on another server - the whole task runs in 40 seconds when executed by clicking on the DTSX package).

The SQL Server agent is running on the destination server, and the connection is through a local network - I am remote from this network, but both machines can see each other on 192.168.1.##.

I'm trying to understand what would cause the login to happen more than one time for this single task - I have set up Full login auditing on the destination server, and the logs look like this (I have truncated the screenshot to hide names, however in all of these logins it was the same username):

It also shows multiple logins for the NetworkService account when I remotely connect to the SQL Server - so not just limited to this DTS task, just more pronounced here - and there is no specific issue with timeouts elsewhere!


texts: log_statement_stats: What is the significance of values in enclosed in brackets?

texts:
I'm reviewing the output of log_statement_stats and would to like to understand the following questions.

[0.526408 user 0.112125 sys total]
=> The previous line already shows CPU seconds of user and system. What is the significance of this line?

Every other line as values in brackets. What is the significance of those values in enclosed in brackets?

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

Thank you


texts: Should these two indexes suggested by the SSMS missing index feature be combined?

texts:
I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework.  I have run the query through SSMS, and it has suggested creating two missing indexes.  The table in question:

CREATE TABLE [dbo].[PackageEvents]
(
[EventID] [int] NOT NULL IDENTITY(1, 1),
[PackageID] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Notes] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsSynchronized] [bit] NOT NULL CONSTRAINT [DF_AmazonPackageEvents_IsSynchronized]
  DEFAULT ((0)),
[LastSyncDate] [datetime] NULL,
[Version] [timestamp] NOT NULL
)

SSMS has suggested the following two indexes:

CREATE NONCLUSTERED INDEX [IX_IsSynchronized] ON [dbo].[PackageEvents]
([IsSynchronized]) INCLUDE ([PackageID])

CREATE NONCLUSTERED INDEX [IX_Covering] ON [dbo].[PackageEvents] ([PackageID])
INCLUDE ([EventDate], [EventDescription], [EventID], [IsSynchronized], [LastSyncDate],
[Notes], [UserName], [Version])

I'm not posting the query I'm optimizing for because it is horrendous (generated by Entity Framework) and is almost unreadable.  In general the query is looking for any rows where IsSynchronized = 0 and returning those rows.

Is there a way to combine these two indexes into one index that would provide the same or better performance benefit?  Is this question impossible to answer without the exact query?

EDIT: The only existing index is the clustered index on the primary key, EventID.

I searched through the generated Entity Framework query.  The following where clause occurs 4 times, but always in the same form:

 WHERE  EXISTS (SELECT
            1 AS [C1]
            FROM [dbo].[AmazonPackageEvents] AS [Extent46]
            WHERE ([Project30].[PackageID] = [Extent46].[PackageID]) AND
           (0 = [Extent46].[IsSynchronized])
        )

Which seems to boil down to

WHERE PackageID=@PackageID AND IsSynchronized=0


texts: Postgres 11 - Range Partitioning on a Date Column with NULL values

texts:
We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we have a requested date column we do all of our querying by but the only records which have a value for this are the parent records.
Since all of our queries are filtering on the parent results initially and use this date, we want to paritition the table on this date range but understand this isn't possible due to there being null values on the records for the child records.
Aside from creating a "dummy" value (some arbitrary date that would never be caught in our filter) on each and every child record in the database, what other option do we have? Is it not possible to somehow create a separate partition for all of the null values that we never query for anyway?


texts:
I have created my CA, Server and Clients certificates and keys. I have tried connections using sslmode flags with psql. I have edited the pg_hba.conf file to force SSL using hostssl only. Now I would like to set my server in order to force the verification to be sslmode=verify-full. I have read twice the documentation I and do not understand how to proceed. They say that it is controlled by an environment variable, but just cannot make it work. How must I set this variable and how would I make it persistent. Do anyone know how to proceed?

Update: I am runing a PostgreSQL 9.3 on Ubuntu Server 14.04


texts: Game database- many to many relationship

texts:
I am designing a simple relational database about a game that can be played by 2 players each

time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a

many-to-many relationship between Player and Game and one-to-many between Player and Payment

since one player can make many payments whereby one payment can't be made by more than one

player. In PlayerPlaysGame table I have only playerID and gameID attributes. Can I also add

gameDate and gameScore to that table or is it better to have them in Game table?

I'd like to ask if I'm on the right track.

Thank you.


texts: How to tune the query on all_constraints in Oracle 12c?

texts:
How to tune the following query, as it's taking around 8 seconds:

select constraint_name,table_name
                from  all_constraints
         where  r_constraint_name in
                               (select  constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER');

SUPPLIER is an example table name.


texts: Can you give me one example of Business Intelligence?

texts: I don't really understand what Business Intelligence is all about. If I start from having a corporate DB, what is it that a BI person would do? I found plenty of material on the web, but it usually is a bit too complex. I want a simple example that would make me understand what BI is all about and what would a BI person produce that is of value to my organization.


texts: SQL Server Agent Job - Adding Version Number

texts:
I would like to know how to add a version number to a SQL Server Agent job without using the description field, and with it being an attribute of the job.

A similar question was asked at StackOverflow Sql Server Agent Job - Adding Version Number  but that question did not include the criteria of not using the description field.  (accepted answer implies the description field was viable solution)

I have a script to update jobs, and I want to capture version information without overwriting any existing descriptions and being able to search for current version on a single field (without combing existing/new comments with version info)

I can use sp_update_job to update most fields, but the only one not in use that will take strings is @category_name and it is limited to values in sp_help_category. (Edit following day >) It can be updated with sp_add_category but that presents the value in the GUI drop down as available for all jobs. Possible, but suboptimal.

I can use sp_update_job this would be suboptimal as steps are parts of jobs.  I don't see any reasonable solutions in there.  I did consider creating a step named "Version 1.0.0" or similar but that was wrong on many levels. EDIT After much research and testing it became clear that this was the optimal approach.

You can not use sp_addextendedproperty to hold a version number in a job. Doing so would require changing the value of 'level1_object_type' to 'JOB' which is not an option. Source

I can use a table to list modifications. But that would not be an attribute of the job, and is subject to human error insertion.  Possibly I might use a table, where a hash of the command field (@command) and/or schedule is used as a unique identifier.  This would/should be unique to job version, while not a direct attribute it would be a derived attribute.

Solution to apply to SQL Server 2008R2 and later by preference, SQL Server 2012 and later by requirement.


texts: MongoDB GridFS delete operation too slow while mongod process takes up 100% CPU

texts:
I'm using Python (CPython 2.7.3 on Debian 7.8 amd64) with pymongo driver (version 2.7.2) to connect to a MongoDB (db version v2.4.12) database which has solely been used to store files in a GridFS storage collection. While mongodb can find the _id of a file object with a given filename instantaneously, requesting to delete that file given its _id takes an unusual 10s of seconds to complete during which mongod takes up 100% CPU time.

I'm not a mongodb expert and have not done any performance optimizations on it, but still I think there is something wrong here. What am I missing and how can I pinpoint and solve this problem?

I should add that there are millions of files in this collection with nearly 700GB size, and the free space left on disk is running very low.

Edit:

> db.stats()
{
        "db" : "imagedb",
        "collections" : 6,
        "objects" : 80435832,
        "avgObjSize" : 6521.6805692766375,
        "dataSize" : 524576802628,
        "storageSize" : 563036266512,
        "numExtents" : 308,
        "indexes" : 7,
        "indexSize" : 6604172176,
        "fileSize" : 588057149440,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
                "major" : 4,
                "minor" : 5
        },
        "ok" : 1
}


texts:
I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding columns to upper select.

select j.name, m.rescount
    (select count(*) as rescount
    from depot_resource as m
    where j.scheduled_time between m.actual_start and m.actual_end)
from transit as j


texts: What is the proper type for columns holding GeoJSON?

texts:
What data type do you prefer for columns holding GeoJSON information, in the major RDBMS families (i.e. Oracle DB, PostgreSQL, MySQL, SQL Server, SQLite, DB2..?

In general, do you prefer / is better using large object columns (e.g. CLOB of Oracle DB) or do you prefer / is better using data types from the character / text family (e.g. text in PostgreSQL or character varying (with unspecified length) in PostgreSQL too)?

Can you be so clever, experienced or knowledgeable even to assert whether the answer depends on the max GeoJSON character length in the table, or not? or maybe the median value or something else?

Spoiler for X/Y problem fans follows!

   For the X/Y problem fans, my X question to this Y question would be:
 Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?, but I guess the answer to my X question is kind of a time evolving one, while I guess the answer to my Y question is more of a constant (even almost axiomatic) one.

I would have created the geojson tag if I already could, but I can not yet so I tag as JSON.


texts:
I've recently installed Cumulative Update 6 for SQL Server 2014 SP2. I installed it on the passive node of a 2 node cluster. To install the patches I paused the role in cluster manager and ran the setup from command line with:

/SkipRules=Cluster_IsOnlineIfClustered /action=patch

I now need to uninstall CU6 as we've come across some issues we need to resolve. However, I am unable to uninstall the patch as I cannot get past the rule checks. If I run the same command to start setup the UI does not give me the uninstall/maintenance options.

Is there anyway to skip rules to uninstall a patch, or am I going to have to remove this node from the cluster before I can proceed?


texts: How do I use "OLD" or "NEW" in a dynamic SQL statement in an on-update trigger?

texts:
Assuming I have the following function being executed on a row-update:

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES (OLD)';
    EXECUTE sql;
END;
$$ language 'plpgsql';

The above example does not work because OLD is unknown in that execution context. So I tried something like:

sql := format('INSERT INTO backup_table VALUES (%L)', OLD);

and

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD));

and

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD.*));

All without any luck.

This issue is part of a larger trigger I am working on. Looked in isolation it does not make much sense, but illustrates the question.


texts: Removing older components after upgrading

texts:
I will upgrade to SQL Server 2016 from a SQL Server 2014 in my company.

This server has had many versions of SQL Server (2008, 2012, etc).

What should we do with the old version's components? When a version is upgraded, older components are left behind.


texts:
I have a Mysql server where the same big table keeps crashing now and then, usully during high load. The table in phpmyadmin gets marked as crashed and need to be repaired. Usually i have to do a myisamchk -r table.MYI to repair it which takes quite a while.
That table is about 6.4 GB with 23.500.000 rows in it and running MYISAM. Is my best bet to convert that table to INODB and how much will that help? or is there anything i can do to my MySQL config to help?
The server has 8GB of RAM and My MySQL config looks like this.
[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]
log-error=/var/lib/mysql/server.servername.com.err
performance-schema=0
table_open_cache=2000
innodb_strict_mode="ON"
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
open_files_limit=40000

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 64M
myisam-sort-buffer-size        = 64M
myisam-recover-options         = FORCE

# SAFETY #
skip-external-locking
max-allowed-packet             = 128M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 32
max-connections                = 500
thread-cache-size              = 286
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 512
group-concat-max-len           = 1048576

# INNODB #
#innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-log-buffer-size         = 16M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 4G

# LOGGING #
# log-queries-not-using-indexes  = 1
# slow-query-log                 = 1
# slow-query-log-file            = /var/lib/mysql/mysql-slow.log

max_allowed_packet=268435456
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet             = 128M

[myisamchk]
key_buffer_size                = 768M
sort_buffer_size               = 256M
read_buffer                    = 2M
write_buffer                   = 2M

[mysqlhotcopy]
interactive-timeout


texts: SQL Server Identifying Connections that are utilising SQL from an application

texts: Is it possible to identify connections in SQL Server 2014 that are utilising SQL that is passed from an application as opposed to calling an SP ?


texts: Find Number of Records Higher Than A Specific Number In Other Table

texts:
I have two tables with following structure:
First Table:

ID    Name    Total_courses_allowed
1     Ahmad     4
2     Khan      3
3     Mahmood   2

Second Table:

ID   Course_attended   emp_id
1           f             1
2           t             1
3           f             1
4           t             1
5           f             2
6           t             2
7           t             2
8           f             3
9           f             3
10          t             1
11          f             1
12          t             2
13          f             2

For each record in first table there should be as many records as their total_courses allowed in second table. but for some of the records it is created more than that their allowed number of courses to attend like for, ID 1 of first table 6 rows are generated in second table whereas it should be only 4.

I need a SQL query to find all the records which have more entries in the second table than their total allowed courses to attend.

Thanks,


texts: What are the main differences between InnoDB and MyISAM?


texts:
Consider the following code in Microsoft SQL Server 2012:

INSERT INTO [dbo].Production
SELECT [field1]
      ,[field2]
      ,cast([datefield] as datetime)
FROM [RAW].Staging

The staging table is loaded with data from a CSV file. So in some cases, rather than having NULL fields we end-up with empty fields. As a result the datefield gets converted to a 1900-01-01 date when being inserted over the Production table.

I was wondering if there is something that I could do during the insert code above to ensure that datefield is set to NULL if the field is NULL or empty when coming from the Staging table.


texts: Rock-Paper-Scissors-Spock-Lizard statistics DB design, is there a better way to achieve this?

texts:
I am working in a player versus computer web application which is a version of "Rock-Paper-Scissors-Spock-Lizard".

I should be able to provide some statistics like for example:

How many times the user has won, lost and draw?
How many times the computer has won, lost and draw?
How many times the user using the player Lizard has won, lost and draw?
How many times the computer using the player Lizard has won, lost and draw?

And basically the same goes to each player type.

I have made this small DB containing only two tables but I am not sure of it because I need to make a lot of COUNT queries to obtain what I am looking for.

Posibles values for result are:

0: user won
1: computer won
2: draw

And the player types are as follow:

1: Lizard
2: Paper
3: Rock
4: Scissors
5: Spock

So for example, to find "how many times the user has won, lost and draw" I need to run the following queries:

SELECT COUNT(*) FROM match WHERE result=0; // won
SELECT COUNT(*) FROM match WHERE result=1; // lost
SELECT COUNT(*) FROM match WHERE result=2; // draw

But if I want to find "how many times the user using the player Lizard has won, lost and draw" then I need to run the following queries:

SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=0; // won using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=1; // lost using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=2; // draw using Lizard

And I need to keep repeating the same queries all the time for get those types of statistics.

Is there any other approach or a better way to achieve this? I am using MySQL 5.6.


texts:
I replace MongoDB default _id with type string. And now I want to sort my collections, the result _id after sort is:

1
10
100
101
2
20
200

I want the result like sorting number which is:

1
2
3
4
5

What's my option? Is there any way to sort the string like number? Or I have to change my _id type?


texts: Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

texts: What happens to the foreign key relationships on a table if that table is renamed?


texts:
One of my AWS RDS PostgreSQL instance is consuming too much storage even though there is no activity going on the Database. I have logical_replication switched ON, to use AWS DMS to replicate DBs to S3 bucket.

The transaction logs are getting generated which are taking the storage space, but as I said there are no activities going on the DB. That's confusing, can someone please help me understand this? The transaction logs gets cleaned up when I run a dummy update script on the DB. Is there any parameters which I can update to continuously cleaning the transaction log files?

Thanks


texts: ER Diagram Design: Should every entity have a primary key? And can we have sub entity?

texts:
Problem: Design a ER diagram such as:

An item has the attribute: description.
An item can be sold by a company or a person.
A person has the attributes: name, phone and email.
A company has the attributes: company name, address and a contact person who is one from the person entity set.
A contact person cannot sell the same item with the company he works for.

This is my design:

I'm learning and I know this is a stupid question. But please correct and point out what are wrong in my design.

I'm not sure that I should remove the primary key SellerID in Seller entity and add companyID to Company entity and personID entity.
Is the Contact Person entity connected to Person entity correctly?
How can I demonstrate the constraint: a contact person cannot sell the items (distinguished by item ID) his company is selling.

Thank you.


texts: Oracle Trace File Interpretation: enq: TX - index contention

texts:
Background

I've got an application which is doing a large number of concurrent inserts into an Oracle table. The Top Activity view is reporting a lot of index contention and I am attempting to reduce this to improve performance.

The table in question has several indexes, but my suspicion is that the problem is leaf-block contention on the PK which is a monotonically increasing sequence. I know about reverse key indexes, but I'd like to be able to pin-point the problem before I dive in with guesswork fixes.

Trace File Contents

I've extracted a trace from the database when under load. I've included a chunk of it at the bottom, but one part in particular is puzzling me. The largest "enq: TX - index contention" entry accounts for 94% of the total time:

WAIT #47156432773600: nam='enq: TX - index contention' ela= 113183 name|mode=1415053316 usn<<16 | slot=4259845 sequence=43226 obj#=-1 tim=1329387810694927

I don't understand how to interpret this line. Googling has yielded that name|mode=1415053316 means Transaction in share mode (hex TX04), and that obj# would reference the object in SYS.OBJ$.

For the other waits where obj# is specified (see below), it points to the PK index I have suspicions about, but in the largest wait, the obj#=-1.

So, my questions are:

What does it mean for obj# to be -1 in this wait?
What does the usn<<16 | slot=4259845 parameter mean? Is it useful to me?
Does the sequence=43226 tell me anything useful?

More trace detail

PARSING IN CURSOR #47156432773600 len=654 dep=0 uid=41 oct=2 lid=41 tim=1329387810581386 hv=3365236844 ad='2977ccca8' sqlid='31r3hm349aw3c'
insert into AnonymousTable (a, b, c ...) values (:1, :2, :3 ...)
END OF STMT
PARSE #47156432773600:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1329387810581384
WAIT #47156432773600: nam='enq: TX - index contention' ela= 113183 name|mode=1415053316 usn<<16 | slot=4259845 sequence=43226 obj#=-1 tim=1329387810694927
WAIT #47156432773600: nam='latch: ges resource hash list' ela= 3958 address=11249153232 number=91 tries=0 obj#=-1 tim=1329387810698944
WAIT #47156432773600: nam='enq: TX - index contention' ela= 1891 name|mode=1415053316 usn<<16 | slot=655387 sequence=48341 obj#=-1 tim=1329387810700949
WAIT #47156432773600: nam='buffer busy waits' ela= 162 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810701223
WAIT #47156432773600: nam='buffer busy waits' ela= 1572 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810702840
WAIT #47156432773600: nam='enq: TX - index contention' ela= 1809 name|mode=1415053316 usn<<16 | slot=5767194 sequence=85968 obj#=28134 tim=1329387810704709
WAIT #47156432773600: nam='buffer busy waits' ela= 2254 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810707018
WAIT #47156432773600: nam='enq: TX - index contention' ela= 15306 name|mode=1415053316 usn<<16 | slot=4784132 sequence=53965 obj#=28134 tim=1329387810722436
EXEC #47156432773600:c=1999,e=142776,p=0,cr=50,cu=364,mis=0,r=28,dep=0,og=1,plh=0,tim=1329387810724203
STAT #47156432773600 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=50 pr=0 pw=0 time=142726 us)'
WAIT #47156432773600: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=28134 tim=1329387810724288
WAIT #47156432773600: nam='SQL*Net message from client' ela= 363 driver id=1413697536 #bytes=1 p3=0 obj#=28134 tim=1329387810724667
CLOSE #47156432773600:c=0,e=7,dep=0,type=1,tim=1329387810724701


texts:
I have a database of a garage of rented cars with a table called Cars with the following columns :
Type,First_seen_in_garage,Last_seen_in_garage
The table initially have the following rows :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-13 11:12:58
Renault,2002-10-13 09:12:58,2002-10-13 12:12:58
Ferrari,2002-10-13 10:12:58,2002-10-13 15:12:58

Now I have the following row from which I have to update the initial table :
*Ferrari,2002-10-14 08:12:58,2002-10-14 10:12:58*
*Renault,2002-10-13 23:12:58,2002-10-14 23:12:58*
*Lambo,2002-10-13 23:12:58,2002-10-14 23:12:58*

in order to become :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-14 23:12:58
Renault,2002-10-13 09:12:58,2002-10-14 23:12:58
Ferrari,2002-10-13 10:12:58,2002-10-14 10:12:58

Note that for the Ferrari rows I want to keep the first instance as it is and update only the second one
I could do it with an UPDATE .. SET .. WHERE if there was only one Ferrari instance however the previous query updates the two rows which is not what I want ..
Help Please.


texts: Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

texts:
I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] respectively). However, whenever there is no event/season going on, I'm not sure whether I should create an special row in my Events table to handle such scenarios or just leave the related column of Items empty.

I've been researching about the topic and I have several found reasonable arguments for either case (to use or not to use a null), but the problem is that none of what I've read so far is framed in use-case that would apply for my specific dilemma. As a result, I'm now even more confused as to what to do. I was originally going to add a "Non-Event" row to my table.


texts:
I've read about an SQL option of cascading permissions when issuing a REVOKE statement. I've googled for how this option is implemented in MySQL, but haven't found it at all.

This is the docs page about the REVOKE syntax and there is no sign of cascading option.

The SQL standard cascade syntax has the following format:

REVOKE [GRANT OPTION FOR] privileges ON object FROM user {RESTRICT | CASCADE}

Is it possible to cascade permissions revoking in MySQL?


texts: Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

texts:
This question is related to the following 2 SQL errors (line breaks added by me)

MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;
this is incompatible with DISTINCT

MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

My problem is simple to describe, even without posting the original SQL. I have a Mysql 5.7.11-log server running on Windows in which I can SELECT A,B GROUP BY A and SELECT DISTINCT A,B ORDER BY C without any problem. Yes, this is wrong from the SQL point of view.

But it works and customers are happy. It will never be fixed be fixed in the future.

We just installed a fresh standard Mysql 5.7.13-log on Linux using Yum. Now all queries containing such errors break.

I have looked around and it seems that Mysql team did some work about improper GROUP BYs. I have tried to disable full group bys but then I am left with the distinct issue.

I have also tried to replicate part of the Mysql configuration from the Windows box but returned again to both issues.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I just want to know how do I tell the new Mysql 5.7.13 to behave exactly like the (bugged?) version 5.7.11. We will try to install the exact 5.7.11 from Yum but it could take a considerable while. So to be more clear: how do I get rid of those two errors, humbly given that the SQL statements need to be eventually fixed?


texts: How to deal with SQL Server Full Text Log Files?

texts: We use SQL Server full text search functionality.   As a result we have lots of full text log files like SQLFT0003200286.LOG.5 in the sql server error log folder.  Is there anything we can do to ensure these files are periodically purged (oldest files purged first)?


texts: Database schema for job offers with some common attributes

texts:
I'm trying to design a database in order to store IT job offers. My data comes from 4 different websites and unfortunately only part of their attributes (column names) are common. It looks like this:
Website 1: title, overall experience level
Webiste 2: title, overall experience level, skills required, skills nice to see
Website 3: title, overall experience level, skills required, level of required skills (junior or mid or senior)
Webiste 4: title, overall experience level, skills required, level of required skills (in years), skills nice to see, language, level of langauge

I would like to know how would you design a database with this kind of data? I did some research and found that it can be done in different ways:
Approach 1: only one table with all attributes (there will be a lot of nulls) but to be honest I think it's bad idea.
Approach 2: one table storing common attributes to all websites and rest of attributes in separate tables. Also I must notice that each job offer has multiple values in skills required and skills nice to see attributes so I think these two must be in many-to-many relationship with JobOffer table

Approach 3: Four tables with attributes assigned to each of websites (so each table represents each website with its atrributes). And like in previous approach skills required and skills nice to see are in many-to-many relationship with almost (except WEBSITE1 table) every website table

Approach 4: Your own approach because none of these above fit well to this problem. (I've never been designing database before so I'm aware that all 3 approaches proposed by me could be wrong)
Thanks in advance!


texts: Finding exact statement causing error 3930

texts:
Lately I've been seeing errors in the SQL Server Log.

  Code:3930 Description:"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

Using SQL Profiler, how can I find the exact statement that is causing this error? Which column in SQL Profiler fills the "Message" column in the SQL Error log? Unfortunately, we are only getting this in production, so I will need to run a trace there with the lightest weight trace possible. What do you folks recommend to capture the offending code?


texts: How to install MongoDB database as a service in ESxi?

texts:
I want to know is it possible to install mongodb directly to the ESXi without guest OS?
Please tell me how to setup Data storage as a service environment inside ESXi.


texts: Why can't I access IIS Manager on my server with Reporting Services installed?

texts:
I have a Windows Server 2008 Server running SQL Server 2012 (including SSRS). I've noticed that the site is intermittently slow on first run, and found this blog post about the cause and how to fix it.

However, when I open the server manager for either of my servers (dev and production) they don't seem to have IIS installed.

Production:

On my dev server, I installed the IIS role, but I can't see anything to do with Reporting Services there:

Why can't I see anything to do with SSRS here? The web sites for both servers work fine.


texts: How to use pgloader to transfer sqlite to postgres with table name in Uppercase

texts:
I can use pgloader to transfer my sqlite database to postgres as described in this quickstart page.

But the result is all my tables' names are lowercases. e.g. TestName -> testname

Is there any way I can preserve the uppercase in my table names?


texts:
I know COPY is the best utility to export the data from a table or a query to output files. But if we are doing an export like
\COPY (select * from table) to '/tmp/file.csv' WITH CSV;

How its different from running the select query and save the results later(like in a GUI tool) or psql -c'select * from table'> output.csv
Any advantage of using COPY in terms of performance and caching?
The overall idea is, I'm trying to understand the performance benefits of exporting the data via read from select vs COPY from python psycopg


texts: Query to get duplicate records then update it by putting a sequence number


texts: Does SQL server log when and who enabled / disabled jobs by default?

texts: Is there any default logging or history of when a SQL job was enabled or disabled?


texts:
I am trying to transport a tablespace in RMAN. The command I'm running is

transport tablespace usr_table auxiliary destination '/oracle/rman/auxFiles' tablespace destination '/oracle/rman';

The initialization parameters used for the automatic instance are

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=tnpD_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/oracle/rman/auxFiles
log_archive_dest_1='location=/oracle/rman/auxFiles'
#No auxiliary parameter file used

The error I'm getting is

RMAN-03002: failure of transport tablespace command at 06/10/2020 15:32:40
RMAN-04014: startup failed: ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3822
Additional information: 2130706432

I need to change the

diagnostic_dest=/opt/oracle

Should I make an auxiliary file and if so do I need to put all the parameters that are shown in the rman output?


texts:
I have the following table

id  |   ev_id   | ev_loc_id |   a   |   b   | prev_a    |   prev_b

If I the (ev_id AND ev_loc_id) not exist in the table then I just insert them, otherwise I'm putting the a to the prev_a and b to prev_b.

I solved this with a selection:

SELECT a, b FROM tb WHERE ev_id=XX AND ev_loc_id=XXX

If the array is empty than I inserted the new data, otherwise I replaced the a,b and the selected values to prev_a, prev_b

Is there most time efficient solution?


texts:
I have the following recursive query:

SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path
FROM user_group_nodes
START WITH parent_id = '2892107544AA2278166C1AA8D123E761'
CONNECT BY parent_id = PRIOR object_id
ORDER SIBLINGS BY object_name;

Which returns the following sample dataset:

\UserGroupA\UserGroupAA\UserGroupAAA\JaneDoe
\UserGroupA\UserGroupAA\UserGroupAAA\UserGroupAAAA\UserGroupAAAAA\JohnDoe

Based on the above sample dataset, I need to insert the following records into a table:

Group            User
---------------  ---------------
UserGroupA       JaneDoe
UserGroupAA      JaneDoe
UserGroupAAA     JaneDoe
UserGroupA       JohnDoe
UserGroupAA      JohnDoe
UserGroupAAA     JohnDoe
UserGroupAAAA    JohnDoe
UserGroupAAAAA   JohnDoe

Can someone provide direction on how to accomplish this? I'm leaning towards using a cursor, but I'm hoping there is another option that performs better since I need to do this across 10,000 users and 1,500 groups.


texts: Selecting the popular posts using three tables

texts:
I have three tables:

posts - contains the posts by users,
post_reviews - contains the likes on the posts, and
post_share - contains the post id and user id of that which is shared.

I would like to select five popular posts using these three tables. The popular posts are those that have the highest no of reviews and shares. I am finding it really hard to construct a SQL query for this.

A slight help or guidance in this matter will be highly appreciated.

UPDATE:

The table structure is as follows:

table posts{

id - PK
uid - FK
post - text
}

table post_reviews{

id - PK
pid - FK (Post Id)
uid - FK
like_unlike - ENUM
}

table post_share{

id - PK
pid - FK (Post Id)
uid - FK
}


texts: JOINS - optimize cost of LIKE/OR criteria

texts:
The Active table has the Account Name in 1 column and the Master table has the Account Name in any one of 6 separate address columns and I'm trying to match them.  Also, they don't necessarily exactly match, so I'm using the LIKE operator.

The Active table is about 250,000 records and the Master table is about 1,500,000 total records, but the account range specified in the WHERE clause is about 5,000 records.

The query takes a long time, so I assume I'm doing something dumb and so I'm trying to figure out a way to optimize it.  The COALESCE function was a late addition added out of desperation, so I'm not sure I'm even using it appropriately.  Originally everything was broken out separately with OR operators. Every field involved is nvarchar.

SELECT
Active.AcctNbr, Active.Name, Master.Addr1, Master.Addr2, Master.Addr3, Master.AcctNbr

FROM Active

INNER JOIN Master ON
Active.Name LIKE COALESCE (('%' + Master.Addr1 + '%'),('%' + Master.Addr2 + '%'),('%' + Master.Addr3 + '%'))

WHERE LEFT(Master.AcctNbr,3) = '123'


texts: Encrypting and Decrypting by Certificate Not Working

texts:
I am trying to encrypt some data in SQL server using an existing key and certificate.  I know this key and certificate work as I'm able to pull information from another table in the database but for some reason my SQL below isn't working.  Am I doing something wrong?

declare @encryption table (
string varbinary(max)
 )

open symmetric key aaaa decryption by certificate bbbb

insert into @encryption
select  encryptbykey(key_guid('aaaa'), 'Test', 1, convert(varbinary,
'1234'))

select  string,
    convert(nvarchar(max), decryptbykey(string, 1, convert(varbinary,
'1234')))
from    @encryption

I'm just getting this from the results.

string = 0x006924CD26547347A19BCA9B53E93E4501000000B01C808AB6802A5FD2781671BF50745638EDC8A55D0DC302F6CA9E3B7AE80913A297D0E34F14F4EA87B7E88F8066C38D718CA2E3092DF8A4C364CD8955CE61B7

decrypted string = 敔瑳

Appreciate any advise / help!

Thanks


texts:
What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.

That's okay to answer of one engine.

TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)

TABLE: `game`
`idx` bigint 20 (autoincrement PK)
`gameId` bigint 20 (UK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)


texts: Get list of students having actions in Oracle

texts:
I have a table of student actions (id, student_id, type, and quarter). I need to get a list of all the students, along w/ a flag of whether they had a particular action in the current quarter, and the past quarter but I'm not sure how to do this in Oracle SQL.

student_actions table

Assuming that the current quarter is 3, and that I'm only interested in the Expulsion and Probation actions how would I get a result set like the one I've mocked up below?

expected results


texts:
We have several servers that sit in another site (across a Site-To-Site vpn connection). When we attempt to connect to the server by IP, it will frequently throw an error during connection (the error usually mentions a wait timeout).

For instance, we're seeing this one today:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake (provider: SSL Provider, error: 0 - The wait operation timed out.)

It doesn't happen consistently on all machines (mine is able to connect by IP today), but everyone experiences it. Additionally, if it does connect by IP, it tends to be incredibly slow.

If we connect by server name instead of IP, this tends to work a lot better. Whilst we might still see timeouts/slow performance, the frequency is a lot lower.

It seems to affect multiple versions of SQL Server too. Specifically, the servers are SQL 2014 and SQL 2012.

Other network tasks across the two sites appear to be working without issue (e.g. file copies, etc)

What could be causing this, and whats the best way to resolve it?


texts: Foreign Key Constraint into an different database

texts:
An SQL server hosts all the data for multiple entities (e.g sub-companies or organizational units OU).
An "enterprise" database holds the "enterprise" data such as configuration data, data about processes that are enterprise-wide ones, and at least one lookup table which stores the data about all the OUs.
The initial design called for the option to be able to split the database of some or all OUs into their own database server that can even physically reside in a different location. So far, both databases reside on the same SQL server.
Every table has a column identifying the id of the OU (OUId) as an FK for OU information and will enable to make the split in the future.
Would you replicate the OU table instead of setting an FK to an external database? How much performance hit an FK to an external database can cause?
Thanks


texts:
I recently made a rather stupid mistake and corrupt my Ubuntu installation.  To resolve it, I booted with a live cd and copied the database files over.  However, now I have copied the folders from the old installation into the new one, whenever I type "use database_name" it just freezes on this:

mysql> use my_database_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

And then freezes without giving the terminal.


texts:
I have a Windows MySQL server running (5.6), and in the my.ini file I have the following directive under [mysqld]:

lc_time_names = 'fr_FR';

But it has ZERO effect. When I start up the server, all time names are still in English. If I then launch Workbench and issue the following command:

SET GLOBAL lc_time_names = 'fr_FR';

it works and the times are changed. Is there something different I need to do in my.ini?

UPDATE (VERY IMPORTANT):
in addition to needing to put this value in the correct ini file, you also need to NOT ADD QUOTES!! I broke my server with the above, and it took me forever to figure out I needed to change the line to

lc_time_names = fr_FR

WITHOUT THE QUOTES, or your MySQL server will not start up! Hope this helps someone else.


texts:
The text below is from the book Database System Concepts by Silberschatz.
This information that is written out to disk forms a log file or is something other that the log file?

The database system then writes out enough information to disk that,
even in the event of a failure, the updates performed by the
transaction can be re-created when the system restarts after the
failure. When the last of this information is written out, the
transaction enters the committed state.


texts: Multiple Publications for Merge Replication?

texts:
I am designing a system which will have one SQL server 2012 central database.

Each client will user merge replication to take an offline copy of their data to edit on the road.

If I add a publication I can add filters to that publication. The thing I can't work out though is that is only one set of filters.

For each client I have they will have a different set of filters to filter data that relates to them only. This is quite important from a security point of view.

How is this achieved with only one publication?


texts: Order of composite key for best lookup performance

texts:
I have a table with a composite key made up of two fields: A and B.

A is like a user ID or machine ID, it is globally unique. B has only a few possible values (less than 5). The composite key will look like (user1 + B1), (user2 + B1), (user3 + B1), (user1 + B2) etc. I don't want to split the data into a few tables by B since the number of rows has not reached that amount yet.

For optimal lookup performance, what should be the order of the fields in the primary index? AB or BA?


texts: Why not use SQL_Latin1_General_CI_AS for a global system?

texts:
What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data?

I'm having a really hard time finding good sources which compare collations and answer my above question as well as the following

How is Japanese_CI_AS different from SQL_Latin1_General_CI_AS when it comes to sorting non-Japanese characters?
Does UCA sort differently than Japanese_CI_AS and SQL_Latin1_General_CI_AS?
What is standard industry practice for systems used globally? (For example, what does Facebook, Twitter, Google, Flickr, Baidu or Microsoft, IBM, and SAP use?)
Does SQL_Latin1_General_CI_AS define a sort order for Japanese characters? If not, how/why does Japanese text work in SQL_Latin1_General_CI_AS?

Basically I'm trying to learn how to pick the appropriate collation :)

Thanks in advance!


texts:
I have two tables

mysql> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mari  |
|  3 | Sami  |
|  4 | Kate  |
+----+-------+
mysql> select * from dep;
+----+------+-------+
| id | eid  | name  |
+----+------+-------+
|  1 |    1 | Tom   |
|  2 |    2 | Tom   |
|  3 |    1 | Paul  |
|  4 |    1 | Karl  |
+----+------+-------+

dep(eid) is foreign key references emp(id).

Question

What is the query to get all employees who have a son his name is Tom given that s/he doesn't have a son with name Paul (the result is Mari)
What is the query to get all employees who dont have a son his name is Paul (the result is Mari, Sami, and Kate)

I know it can be handled using sub query very easily but i am looking for the join structure


texts: How to find out what is causing my Identity column to keep going up

texts:
I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is.

To give a quick example, yesterday I inserted a record and got ID 41,773. Today I inserted a new record and I got ID 44,898. There are no records with IDs between these.

There are lots of apps with access to the database, so I'm in the process of checking their logs for errors; however, I'm wondering if there is a way I can find out what queries are causing the IDENTITY increment without a record being inserted.

There are no explicit deletes on this table either, so I know its not an insert + delete combo, causing this, it has to be something failing.


texts:
I am working on a project trying to consolidate two databases, DB1 and DB2, for my organization into a single database, DB3.

Initially our organization started with DB1.  A few years back they made a new database called DB2 and moved on to using only DB2.

Currently, I am working on the Orders Table.

Since new rows were inserted into DB2 without importing existing rows from DB1, Order-id Column values started from 1 instead of continuing from where the Order-id Column in DB1 had ended.

I have many Order-id Column values that are the same in both Tables that represent different orders.

For example Order-id = 100 in DB1 refers to order "X", whereas Order-id = 100 in DB2 refers to order "Y".

If I insert rows from both DB1.Orders and DB2.Orders into DB3.Orders there will be duplicate rows representing different orders.

As I have not come across this sort of situation before, I need a suggested approach for  dealing with this.


texts: Creating and querying a database to set hierarchical associations between Groups, Users, Items and Points

texts:
I am working with three entity types, Group, User and Item. A Group consists of Users, and a User consists of Items.
Now, I have to work with a fourth entity type: Point. I want to be able to distribute these Points on a Group, User and Item level. A Point is an entity that can be assigned on many levels.
Say I have a Point in "Group 1", then all users and items have access to that. So let's say I add a new user and attach it to "Group 1" then it would automatically have access to that point in the application because it's a member of "Group 1".
Assuming that I want to give a point to "Group A" then a point should be associated with the group, all the users and the items of those users.
If I, for example, give a point to "User 2" then the point is associated with that user and all the items associated with that user.
My idea for the three levels is dynamically adding new data and hence inheriting those permissions while having the ability to add the Point permission all the way down to a Item level if you decide that just a single item should be able to see it.
How would I go about making a data scheme for this scenario?
My attempt so far
Entity-relationship diagram
My attempt at a diagram of how the significant entity types would connect in my head:

Data manipulation
Example with assigning GROUP 1 to POINT 1:
INSERT INTO Point_group
   (group_id,point_id)
VALUES
   (1,1);

Example with assigning USER 1 and 2 to POINT 1:
INSERT INTO Point_user
    (user_id,point_id)
VALUES
    (1,1);
INSERT INTO Point_user
    (user_id,point_id)
VALUES
    (2,1);

And then write some logic to determine what entries are marked based on that. I feel a bit lost.


texts:
On my production SQL Server Enterprise Edition database 2014, I am trying to find why data files (.mdf) growing day by day.

I get the following report From Management Studio: right click on the database> reports > standard reports > disk usage by top tables.

I found some tables contain more than 70 million rows. One table takes more than 13625168 KB - almost 12 GB! What should I do?

Is there away to optimize the size of table even if contains a huge number of rows?

Some data is needed, other data only needed for archiving purposes.

Updated:

Can I Compress the huge table?


texts: Passing array parameters to a stored procedure

texts:
Possible Duplicate:
  Technique for sending lots of data into stored proc

I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.

For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.

Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.

Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.

Is there a better way to do this?


texts: SQL Server Database Hot Backups: Which Technique(s) to Use for Multi-Level Replication?

texts:
I'm reading the many other posts here about mirroring vs. replication, but I'd still appreciate it if someone with some experience would weigh in.

My small development team needs to have local automatic synchronization of databases which are hosted remotely.

The network situation is a little complicated:

The actual databases (dev, test, prod) are being hosted remotely on VMs in a DMZ.  Our team does not have direct access to the DMZ.
Instead we have VPN access to the host's internal network, and the firewall between the internal network and the DMZ currently only allows RDP to pass through.
Locally we have a development database server, and individual developers have local SQL Servers on their development laptops.

We have requested and received an always-on VM in the remote host internal network that we plan to use to monitor the health of our dev/test/prod servers as well as to maintain hot backups of our databases.

So we are looking at two levels of data synchronization:

Constantly (but asynchronously) updated copies of dev/test/prod databases on our monitoring VM.
Occasionally-updated copies of the databases on our local development database server and/or developer laptops.

We aren't looking to implement fail-over at this point, because even the "prod" environment is not critical (it's more of a pre-prod environment).

We also need this to be as simple as possible, and SQL Server 2012 is not an option. We are stuck with 2008.

Here's what I'm thinking:

Implement database mirroring between our dev/test/prod database servers and our always-on monitoring VM in the host's network.
Implement log shipping to occasionally (when on the VPN) synchronize our development database server and laptops with the DB copies on the always-on monitoring VM.

My questions are:

Is this the best and/or simplest way to accomplish what we are trying to do? If not, what is better and why?
Is it even possible to do log shipping off a mirrored database?
What ports are required to be open in order to implement database mirroring (or log shipping if that's better) between dev/test/prod in the DMZ and our monitor VM in the internal network?

Thank you for your time.


texts: PostgreSQL undocumented timenow() function?

texts:
Looking at the documentation of PostgreSQL I couldn't find anything on timenow(). Yet if I call the function it works.
So what is the difference between now() and timenow() ?
I am going to guess that now() is based on transaction while timenow() is server OS time or am I completely off?
The below is DataGrip's auto generated definition. All system functions (for example sum) all have missing source code and as you can see it's part of the pg_catalog schema.


texts:
I'm trying to optimize the following statement:

'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)

The statement produces a value like VI1/2011/000002 if the @number parameter is 1.

I would like to optimize this in terms of removing redundant cast statements and providing an efficient way to concatenate the strings and integers.


texts: create additional oracle 11g database in linux

texts:
I have already created a database XE on my linux desktop.Please find the below information about the database.

  ORACLE_SID=XE

  ORACLE_BASE=/u01/app/oracle

  ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

I have to create another database,eg: database name is DB1. My question is how i can set ORACLE_HOME for DB1 and other parameters in pfile ?

Also i can avoid the error

ORA-45301: XE Edition single instance violation error


texts: MySQL's "slow query" log: strange "Query_time"

texts:
I need some help understanding what's going on with my "slow query" log in MySQL. I have set

 set global long_query_time=3

and yet this is what I see in the slow query log:

# Time: 120723  9:04:38
# User@Host: root[root] @  [111.111.111.205]
# Query_time: 0.017508  Lock_time: 0.000111 Rows_sent: 2884  Rows_examined: 9132
SET timestamp=1343027078;
SELECT [...]
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.030282  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027078;
SELECT [...]
# Time: 120723  9:04:41
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.004416  Lock_time: 0.000104 Rows_sent: 18  Rows_examined: 6266
SET timestamp=1343027081;
SELECT [...]
# User@Host: root[root] @ hostname [111.111.111.199]
# Query_time: 0.029399  Lock_time: 0.000116 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027088;
SELECT [...]
# Time: 120723  9:04:53
# User@Host: root[root] @  [111.111.111.201]
# Query_time: 0.004470  Lock_time: 0.000080 Rows_sent: 0  Rows_examined: 6248
SET timestamp=1343027093;
SELECT [...]
# Time: 120723  9:04:55
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.042627  Lock_time: 0.000087 Rows_sent: 0  Rows_examined: 0
use [...];
SET timestamp=1343027095;
UPDATE [...]
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.029115  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 0

where the time in Query_time is significantly lower than my prevously specified 3 seconds. Why's that? Where is the missing time?

MySQL 5.1.41, Ubuntu 10.04


texts: How to select the sum from three tables and group by date

texts:
I have 3 tables.  works,software,agent

it shows like that:

agent:

id  name
1   agent1
2   agent2
3   agent3
4   agent4

works

id  name    date(TIMESTAMP type)    agent_id
1   w1  2014/1/1    1
2   w2  2014/2/2    1
3   w3  2014/1/3    2
4   w4  2014/1/4    2
5   w5  2014/1/5    3
6   w6  2014/1/6    4
7   w7  2014/1/7    4
8   w8  2014/1/8    4
9   w9  2014/3/9    4

software

id  name    date(TIMESTAMP type)  agent_id
1   s1  2014/1/1    1
2   s2  2014/2/2    1
3   s3  2014/1/3    2
4   s4  2014/1/4    2
5   s5  2014/1/5    3
6   s6  2014/1/6    4
7   s7  2014/1/7    4
8   s8  2014/1/8    4
9   s9  2014/3/9    4

and I want to get the result like that: get the every month of current year sum of works + software connect by agent_id

Jan-14  Feb-14  Mar-14  Apr-14  May-14  Jun-14  Jul-14  Aug-14  Sep-14  Oct-14  Nov-14  Dec-14
agent1  2   2   0   0   0   0   0   0   0   0   0   0
agent2  4   0   0   0   0   0   0   0   0   0   0   0
agent3  2   0   0   0   0   0   0   0   0   0   0   0
agent4  6   0   2   0   0   0   0   0   0   0   0   0

How to write the sql in MySql?


texts: Postgres - how can I move a large table out of production while maintaining access to production data to interpret and use the large table?

texts:
Our production database includes a table that is at about 100 million records and will likely get to a billion records before too long. Every other table is small--less than a million records.

The large table depends on the small tables. Individual records are calculated from the small tables and can only be properly interpreted using the context the small tables provide.

We want copies of the small tables for use in staging and testing. We also want them for interpretation of archived versions of the large table. It would be easy to get and maintain these copies if the large table weren't in the production db, so it's tempting to move that table somewhere else. However, we want the systems that interact with the large table to have up-to-date information on the small tables.

Is there a nice way to move the large table out of the production database while maintaining its access to updates on the small tables that are in the production database?


texts: IN subselects vs INNER JOIN (performance)

texts:
I have to make a choice:

choice 1: using IN subselect
choice 2: using INNER JOIN

See the examples:

IN subselect:

SELECT contractServiceCode FROM contractServices
WHERE contractID IN (
    SELECT contractID FROM contracts
    WHERE companyID IN (
            SELECT companyID FROM tblcompanies
            WHERE informationProvider = 1000000
    )
)

INNER JOIN's:

SELECT cs.contractServiceCode
FROM contractServices as cs
INNER JOIN contracts c ON (c.contractID = cs.contractID)
INNER JOIN tblcompanies tc ON (tc.companyID = c.companyID)
WHERE
    tc.informationProvider = 1000000

In terms of readability I would say that the INNER JOIN is more readable.
But I'm not worried about readablity. In my case performance / CPU / memory usage are import.

So the question is:

Which choice is better in terms of performance?


texts: cypher query to find Employee nodes who have all the Skills

texts:
I'm trying to write a cypher query to find the first name of 'Employee' nodes who 'Has' all the Skills (have relationship with all the nodes with the 'Skill' label). This is my data model:

I managed to get all the employee nodes that have at least one relationship with skill nodes, but I do not want this. This is the cypher query:

:match (a:employee)-[r:Has_Skill]->(b:profession) return a,r,band

I couldn't write it with the ALL() function.
Can anybody help me with this?


texts:
I have the following three tables in mysql database named "THE_COLLEGE"

mysql> desc students;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STU_Id   | char(5)     | NO   | PRI |         |       |
| STU_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc staff;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| STF_Id   | char(5)     | NO   | PRI |         |       |
| STF_Name | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)
mysql> desc users;

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Id         | char(5)     | NO   |     |         |       |
| user_Name  | varchar(20) | YES  |     | NULL    |       |
| Password   | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

If a new row want to be inserted into the table "users", that row must be found in students table or staff table.

Which means a staff or a student can be a user.

Is it possible?

I tried with following foreign keys.

ALTER TABLE users
ADD CONSTRAINT fky_stu
   FOREIGN KEY (ID)
   REFERENCES Students (STU_Id);

ALTER TABLE users
ADD CONSTRAINT fky_stf
   FOREIGN KEY (ID)
   REFERENCES Staff (STF_Id);

But the user table is allow to insert a row, if the row found in both tables (Student and Staff).

But I need to allow if the row found in any of the table (Student or Staff).


texts: Access "couldn't use ... file already in use"

texts:
I have an Access 97 database that is used by some software on multiple (up to 4-6) computers (running Windows 7). Once in a while (maybe 1-2 months) something happens where computers on the network can't access the database.

The program accesses the database through a ADO/JET driver (the program is written in LavVIEW) and reports "Could not use ... file already in use." When this happens attempting to open the database on another computer in Access gives a similar message:

"Couldn't use '.mdb'; file already in use."

When this happens there isn't an associated .ldb file with the .mdb. The last time this happened I restarted the four computers that were running the software and I was immediately able to open the DB with Access on another computer. It seems that caused the problem to go away, but it is possible it was a coincidence.

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

Does anyone have any ideas of what could be causing this? I think one possibility is the computers actually cannot write the .ldb file (the software normally doesn't have a connection open to the database, so most of the time there isn't a .ldb file). I would have to check that by trying to write a file to that directory next time this problem happens. It seems like a strong possibility is something else hanging it up though, especially since restarting the computers seems to fix it but I'm not very familiar with Access, ADO, and JET.


texts:
I am following word by word of http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html for creating SSL certificates to setup SSL enable replication but it is not working for me. Steps which I am following:

Using above reference I have created all required certificates in /var/opt/mysql_certs/
Changed the ownership of all the certificates chown -R mysql:mysql /var/opt/mysql_certs
Then I edited my /etc/my.conf and put these 3 lines in mysqld section:

ssl-ca=/var/opt/mysql_certs/ca-cert.pem
ssl-cert=/var/opt/mysql_certs/server-cert.pem
ssl-key=/var/opt/mysql_certs/server-key.pem

Lastly I am restarting mysqld with /etc/init.d/restart mysqld. But, I am getting following errors:

[Warning]: Failed to setup SSL`
[Warning] SSL error: SSL_CTX_set_default_verify_paths failed

FYI: OS is centOS 6.


texts:
I'm coming to oracle from postgresql.  In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction:

dbpool.production=> begin;
BEGIN
dbpool.production=*> delete from foo where account_id like '%1000%';
DELETE 9
dbpool.production=*> commit;
COMMIT
dbpool.production=>

I like to see that I got the count I expected before committing the transaction.

Things don't work like that in sqlplus, the Oracle command-line tool.  When I type "begin;", it just starts reading more input, never apparently doing anything:

SQL> begin;
  2  update opacs_work_orders set customer_id = null;
  3  commit;
  4

How can I, using sqlplus, execute one or more statements with a transaction?


texts: Are Views optimized when I add a WHERE clause to them?

texts:
Does it make a difference if you filter a View inside or outside the View?

For example, is there any difference between these two queries?

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

Or

SELECT Id
FROM MyView
WHERE SomeColumn = 1

And MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

And is the answer any different if the source table is located on a Linked Server?

I'm asking because I have to query a large table (44mil rows) twice from a linked server, and get an aggregate of the results. I want to know if I should create two views to access the data, one for each query, or if I can get away with a single view and a WHERE clause.


texts: Will insert and update wait while a table is being vacuumed?

texts:
I want to vacuum a table in PostgreSQL version 9.1 which will take between 10 to 12 minutes.  This needs to be done in real-time, in production use.

I just want to know that if any INSERT or UPDATE query comes, will they wait for the lock to be lifted, or will they time out?

NOTE: The Java code that will fire the query has not specified any timeout.


texts: Mysql - Each mysql database with its own user?

texts: I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to connect to and administer all the databases of various companies in my web app? The companies don't care how I have implemented the backend.


texts: How to Prevent Partitioned Columnstore Deadlocks on SELECT

texts:
I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks on simple select statements from joins to these tables. Example query that deadlocks:

SELECT  TOP 33 r.tenantid
FROM    Table_r r
        INNER JOIN Table_cm cm ON r.MyKey=cm.MyKey
        INNER JOIN Table_pe pe ON r.MyKey=pe.MyKey
WHERE   r.TenantId = 69
        AND pe.TenantId = 69
        AND cm.TenantId = 69

Error message:

  Transaction (Process ID 56) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Clues:

If the query uses another index besides the CCI it does not deadlock.
If I remove two of the three tenantid filters it does not deadlock.
If I SELECT top 32 or lower it does not deadlock.
If I add OPTION (MAXDOP 1) it does not deadlock.
I can repro this in my scrambled PROD replica, PROD READ-ONLY Secondary, and PROD itself.
I cannot repro this behavior in DEV or INT.
It still deadlocks if I add WITH(NOLOCK) to all 3 table joins
The query deadlocks itself. It will deadlock when there are no other active processes.
Query plans without parallelism do not deadlock

Deadlock xml here

Our PROD Version:

  Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)   Jan 10 2019 18:51:38   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

How do I prevent deadlocks on this query?