texts:
I am new to DBMS and was reading about the ER Model and entities-attributes. I found the following line in the book which I was reading:

The domain of Composite attributes is the cross product of domains of
component attributes.

Which is fairly straight forward and intuitive. However, I was not able to understand the line following it:

The domain of Multi-valued attributes is a set of subsets of values
from the basic domain.

I couldn't understand this line. I know Power set, which is set of all subsets of a set. Does this line mean, The domain is the power set of the basic domain set? I tried searching on the web before posting, but couldn't find any related literature/ explanation.
A simple language explanation would help! Thank you!


texts:
I am looking at data captured with the help of sp_whoisactive.

session_id: 1016
blocked_session_count: 24
Login_time : 29/03/2018  12:14:59
tran_start_time : 29/03/2018  12:14:59
start_time: 29/03/2018  12:15:59
status: sleeping
open_tran_count :1

The login_time and tran_start_time are the same for this session_id.
But start_time is after a minute.

Does this mean the session entered sleeping state @ 29/03/2018  12:15:59 ??

Thank you :)


texts:
Possible Duplicate:
  Does SSRS lock the table when querying?

I'm using SQL Server 2008 R2 and Reporting Services. Do the reports block other queries or are they executed in highly isolated transactions, such as a snapshot?

Next obvious question: Can reporting services produce deadlocks or timeouts in other queries as a result of locking tables for a long time?


texts:
I am trying to load the following csv file into a table:

    TIMESTAMP          ; SESSIONID;PROXY_SESSIONID;    ACTION;USERNAME            ;USERHOST            ;OS_USERNAME         ;CLIENT_ID           ;ACTION_NAME         ;TRANSLATE(SQL_TEXT,'X'||CHR(9)||CHR(10)||CHR(13),'X')
04-29-2014 21:46:40; 707304320;NULL           ;     3;COMP             ;preview.firma.de  ;console             ;                    ;SELECT              ;SELECT TRIM(LEADING '.' FROM class || '.' || name) AS key_for_value, value AS value FROM config_tbl

my control file is the following:

OPTIONS(skip=1)
LOAD DATA
INFILE 'dump_audit_table.csv'
INTO TABLE tmp_audit_trail
TRAILING NULLCOLS
(
 TIMESTAMP date "MM-DD-YYYY HH24:MI:SS",
 SESSIONID INTEGER EXTERNAL TERMINATED BY ';',
 PROXY_SESSIONID INTEGER EXTERNAL  TERMINATED BY ';',
 ACTION char "TO_NUMBER(:ACTION, '999')",
 USERNAME char(30) TERMINATED BY ';',
 USERHOST char(128)  TERMINATED BY ';',
 OS_USERNAME char(255) TERMINATED BY ';',
 CLIENT_ID char(64) TERMINATED BY ';',
 ACTION_NAME char(28) TERMINATED BY ';',
 SQL_TEXT char(2000)  TERMINATED BY ';'
)

And this fails with:

Record 1: Rejected - Error on table TMP_AUDIT_TRAIL, column ACTION.
ORA-01722: invalid number

I Can't figure why this is an invalid number. #

Update: The following line can be read:

04-29-2014 21:46:40; 707304320;;3;USER;preview.firma.de;console;;SELECT;SELECT TRIM(LEADING '.' FROM class || '.' || name) AS key_for_value, value AS value FROM config_tbl

The solution is: No leading and trailing spaces are allowed!


texts: Show the number of all tables, views and base tables

texts:
How to do that?
I was thinking of something like:

SELECT COUNT(A.table_name), COUNT(B.table_name)
FROM information_schema.tables AS A, information_schema.views AS B;

But sadly it gives me bad numbers (it multiplies the first COUNT with the second one) and I still don't know how to get BASE TABLES.


texts: How to capture PRINT statements from TSQL called by CLR stored procedure

texts:
I have a CLR stored procedure that (among other things) calls a TSQL stored procedure. The TSQL stored procedure runs some dynamic SQL, and it prints the SQL before it runs for debugging purposes. Nothing from the PRINT statements shows up in the client. What is a good way to allow us to see these commands when we need to troubleshoot?

Sample code:

C# code in dll

public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
            conn.Open();
            using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
            {
            c.ExecuteNonQuery();
        }
    }
}

Proc called by CLR

CREATE PROC DBO.Print_TSQL AS
PRINT 'WTF'
GO
--Exposing proc to SQL
CREATE PROC dbo.Print_CLR AS
EXTERNAL NAME
[CLRUtility].[CLRUtility.CLRUtility].Print_CLR
GO
--Executing, nothing is PRINTed for the client
EXEC dbo.Print_CLR


texts: Trying to restore a db2 database but stops at roll-forward with error 'SQL1265N'

texts:
I'm trying to restore a backup that is older than the current database.

The restore went ok, but the roll forward didn't giving the following error:

  SQL1265N The archive log file ".LOG" is not associated with the
  current log sequence for database "" on node "0".

I tried to roll forward to an earlier point in time, but it is required to restore to the end of the logs.

The backup is from another server but it's actually a duplicate (so name etc. are the same.)

What steps can I take to resolve this?


texts: In MySQL, get number of pregnant women who received 3 ANC checkups

texts:
I am working on a project where it is required to find out the total number of pregnant women who have received 3 ANC(Ante-Natal Care) checkups done.

A pregnant women is said to receive 3 ANC checkups if any 3 of the 4 columns(visit1_date,visit2_date,visit3_date and visit4_date) have some value.

For this, I need to calculate the count of number of rows where any 3 of 4 columns given in the table are not null.

So, basically, what is required is to find a count of all rows where any three of

visit1_date, visit2_date,visit3_date,visit4_date are NOT NULL

My table has columns individually for each of the visit dates

I have tried using IFNULL, but I have no idea if this would help.


texts: Bit And Operation in Decimal Column in Impala

texts:
Consider in impala, I have a decimal column, is there a way to convert to binary using bin function
I can see clearly that bin is accepting only big int argument not the decimal
So, if there is a number like 122642280288770432172031 which is crossing the big int limit in impala, how do I convert it to binary form and use a bit and(https://impala.apache.org/docs/build/html/topics/impala_bit_functions.html#bit_functions__bitand) operation on it?
Also, let us know if there is a possibility in Hive if not for impala


texts:
So I have a basic SQL agent job that runs a Robocopy script to move all files from one folder to another.

Job is a pretty basic set up.

With a pretty basic schedule.

And yet it has yet to run. I don't mean run successfully either I mean run at all. Is there any reason this might be the case?

For additional information I'll script out the job as well.

USE [msdb]
GO

/****** Object:  Job [MoveMantisFilesToArchive]    Script Date: 12/23/2015 10:21:52 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/23/2015 10:21:52 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MoveMantisFilesToArchive',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Moves Mantis files to archive. It''s a very descriptive title.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'MyEmailGroup', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Move the files in the afformentioned title.]    Script Date: 12/23/2015 10:21:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Move the files in the afformentioned title.',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'robocopy MySoruce MyDestination /mov',
        @flags=0,
        @proxy_name=N'RunsAs'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'M-F',
        @enabled=1,
        @freq_type=8,
        @freq_interval=62,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20151218,
        @active_end_date=99991231,
        @active_start_time=170000,
        @active_end_time=235959,
        @schedule_uid=N'bcb83273-19e8-49fb-a456-8517642370e3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


texts: Does "Connect" statement for every query indicate broken connection pooling?

texts:
there's only one client application running queries against a MySQL instance.

After enabling MySQL's general query log (configured to log every query) I noticed that every query starts with a "Connect".

Does this imply connection pooling isn't working?

Thx,
Christian.

Edit1:

DB is accessed by PHP application (SugarCRM). It's configured to use persistent connections.

> egrep connections /var/www/html/sugarcrm/config.php
'disable_persistent_connections' => 'false',

> egrep -A7 dbconfigoption /var/www/html/sugarcrm/config.php
'dbconfigoption' =>
array (
'persistent' => true,
'autofree' => false,
'debug' => 0,
'ssl' => false,
'collation' => 'utf8_general_ci',
),

Main Question (not trying to solve it at the moment, just want to understand): If in the log every query starts with a "Connect" - does this then imply that no persistent connections are used?


texts: Where in the database file is an index rebuild started? Alternative to SHRINKFILE?

texts:
When rebuilding an index in SQL server, where in the datafile does it put that data?

Does it start at the beginning of free space and fill in the gaps from there?

Context:  I have some large .ndf files I'm trying to shrink (not important why); but instead of using a SHRINKFILE command, I'm wondering if I can rebuild the indexes and then truncate the file from 80GB down to 25GB.

Edit:  There are many reasons to want to reduce the file space or reorganize it logically.  These may relate to fragmentation, how that fragmentation is translated to IO requests, moving large files across a network, swapping out storage space, how data is read sequentially, etc.  Excessive space usage could even prevent other files from being written to your disk that need the space or dbs that need room to grow.  You might have done some testing and now need to clean up excessive data and the space it required.  Imagine you just switched to a newer database server and could take a 400GB database and compress it down to 100GB with newly available db management techniques, compression, etc,  That's got value and I'm sure there are many other scenarios where this matters.  Just be careful to judge the reasons behind questions please.


texts:
I want to dump Excel data in SQL Server database. What is the best/easiest way to do this?

I have SQL Server 2012 Management Studio. I have tried many techniques but failed miserably, there is no option to connect a link between Excel and SQL Server.


texts: Memory-optimized table index size 10X bigger than data

texts:
On one of our production servers, I found that index size is 10X times bigger than data size.
These servers and schemas are identical with a similar workload.
CREATE TABLE [dbo].[process_aggregation_close]
(
    [organization_id] [bigint] NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
    .
    .
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Tables are staging tables and they are involved in very frequent insert/delete operations.

Microsoft SQL Server 2017 CU9 Standard Edition.
All databases are in the full recovery mode. I do not think it can be related to checkpoint, since the tables are schema-durable only. (In any case, I tried a manual checkpoint with no effect).
These in-memory tables are staging and always insert and delete activities exists on them. I monitor five SQL Servers with similar load on them. This behavior occurs only on one.


texts: Full recovery model and tablock inserts logging

texts:
When testing some queries, I set the recovery model to full on a database and ran two identical 1M row inserts, with and without TABLOCK.

On the one with TABLOCK, I got around 9295 log records on a sql server 2008 instance, and around 8714 log records on a SQL Server 2017 instance.

When running the insert without tablock, I get around 1035659 records for the 2008 instance and around 1068599 records for the 2017 instance.

The reason for testing on sql server 2008 is to match the data loading performance guide's statement on the recovery models for ML operations :

  Minimally logged operations are available only if your database is in
  bulk-logged or simple recovery mode.

So, what am Is seeing here if it is not minimal logging?

Use DatabaseName

ALTER DATABASE DatabaseName SET RECOVERY FULL;
GO
BACKUP DATABASE DatabaseName TO DISK = '\\location\DatabaseName.bak';

BACKUP LOG DatabaseName TO DISK = '\\location\DatabaseName_log.trn';
GO

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO

CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account without TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts  (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);
--1035659 rows
GO

-- clear the log
BACKUP LOG DatabaseName to disk = '\\location\DatabaseName_log2.trn';

GO

--drop the table

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO
-- create the table
CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account WITH TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts WITH(TABLOCK) (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);

--9295 rows


texts: SSIS won't connect to Oracle issue after ODAC driver un-install & re-install

texts:
I installed the ODTwithODAC1120320_32bit drivers and client and could successfully connect SSIS to an Oracle database.

I noticed that the installation had defaulted to the E: drive which is where the data files are on that server.

In the spirit of trying to follow best practice I uninstalled ODTwithODAC1120320_32bit and reinstalled them in the correct drive D:

When I try and connect SSIS to oracle again it fails with the following error message:

"Test connection failed because of an error in initializing provider. Error while trying to retrieve text for error ORA-01019"

I set up the TNS Names in ODBC DataSources and can connect successfully when I test there. So it seems like the problem is in VS / SSIS

Has anybody experienced this before? Any ideas?

Cheers,

Lins


texts: how to open a corrupted progress v9.1e database

texts: How can I open a corrupted PostgreSQL v9.1e database which has data that was added after it was last backed up?


texts: Server load discrepency in Slave with Master load being low and constant


texts: System to track what Zip Codes my vendors service

texts:
I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start.

This will end up as a one-to-many relationship between our vendors (they are waste services companies), and the various zip codes they service. Does anyone have advice on a good way to set up the tables to track this data? Some vendors may only service 5-10 zip codes, while the national players can handle nearly all 45,000 zip codes, so I'd like to set it up in a manner that this does not become a nightmare later on.

Any advice is appreciated!


texts: Can't find names with criteria that has special symbols in PostgreSQL

texts:
I am running the following query which gives syntax error:

select distinct e.ahrims_id,e.card_num,e.name_related,e.d_name,e.father_name,
e.d_father_name from hr_employee e left join unit_identification_code uic
on e.unit_identificatin_code_id = uic.id where uic.code in (
'ZRMAAA', 'ZRMA10', 'ZRMA66', 'ZRMA11', 'ZRMA12', 'ZRMA13', 'ZRMA14', 'ZRMA15', 'ZRMA27',
'ZRMA53', 'ZRMB11', 'ZRMA20', 'ZRMA65', 'ZRMA21', 'ZRMA22', 'ZRMA23', 'ZRMA24', 'ZRMA25',
'ZRMA26', 'ZRMA52', 'ZRMA30', 'ZRMA60', 'ZRMA31', 'ZRMA32', 'ZRMA33', 'ZRMA34', 'ZRMA35',
'ZRMA28', 'ZRMA80', 'ZRMB13', 'ZRMA40', 'ZCCA39', 'ZRMA29', 'ZRMA43', 'ZRMA44', 'ZRMA45',
'ZRMA71', 'ZRMA09', 'ZRMA99', 'Z1200N', 'ZTEC14', 'ZRMA81', 'ZRMA82', 'ZRMA83', 'ZRMA85',
'ZRMA86', 'ZRMA87', 'ZRMA88', 'ZRMA89', 'ZRMA90', 'ZRMA92')
and
(e.name_related = '' || e.name_related like '.' || e.name_related like ','
|| e.name_related like '%x%' || e.d_name='' || e.d_name like '.' || e.d_name like ','
|| e.d_name like '%x%' || e.father_name='' || e.father_name like '.'
|| e.father_name like ',' || e.father_name like '%x%' || e.d_father_name=''
|| e.d_father_name like '.' || e.d_father_name like ',' || e.d_father_name like '%x%'
)

Following is the error message:

  ERROR:  syntax error at or near "like"
  LINE 3: ...= '' || e.name_related like '.' || e.name_related like ',' |...

I googled a lot, but did not find a good solution for this.
Please help if you can.
Thanks in advance.


texts: Ola Hallengren - DatabaseBackup - LOG Unable to open Step output file. The step failed

texts:
DatabaseBackup - USER_DATABASES - LOG: This job fails saying "Executed as user: Domain\XXXX-SVC. Unable to open Step output file.  The step failed."

The error is only with LOG backup job.
The other DatabaseBackup jobs (FULL, DIFF) works just fine with same SVC account. So the service account have appropriate permissions.

The Output File(Job Step properties-->Advanced) is F:\SQLAgentLog\ which is same for all jobs. Only problem is with LOG backup job. Has anyone else experienced this and is there any solution?

Current environment:
SQL Server: 2012 SP3 CU8
OS: Windows Server 2012

Note: This was working all good on a Windows Server 2008!!


texts: SQL Server 2012 Transaction Log needed to create database mirror?

texts:
There's a somewhat lengthy discussion of Transaction Logs in Microsoft's instructions for setting up a database mirror.  We've never bothered with Transaction Logs in the past; do we really need to now?

Isn't the Transaction Log effectively an incremental backup that follows a full backup?

Unless the database drive fails while building the mirror, won't the mirror get from the primary all of the events that occurred while the backup file was being copied and restored to the mirror server?

It seems to me that the full backup alone might be slower, but it would be simpler.

Additional information

When building a mirror, Microsoft's documentation instructs us to restore the last full backup (plus the last differential, if there is one) and all subsequent Transaction Logs.

Unless one puts the database in single-user mode while building the mirror, there will be at least a small activity gap between the last data restored and the time the mirror goes live.

Will the mirror fill in that gap (as it does when it temporarily goes off-line)?

There's a caveat in the instructions if the database path is different on the mirror server: we cannot "add" TLs.  The activity gap could be quite big if one had to skip several TLs.

That's what I meant above: wouldn't it be simpler to ignore the Transaction Logs when building the mirror, and just let the mirror fill in the gap?


texts: MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

texts:
I have a mysql insert on update query like so

insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';

col1, col2 is a composite key and it has index on it.
There are a total of 91 records in table
All other blobs in table have less than 1000 chars
If I give col3 a small value then this query runs instantly
If I give a small value in the insert part but large value in the update part it takes 5 seconds to execute
If I give large value in insert part but small value in second part it dies with a 1 minute timeout.

What could be causing this query to die on execution after being stuck for 1 minute?
What should I look at?

What could be causing this query to take so long to execute and dying if blob value is huge?


texts: Sum every X rows with no common group by parent

texts:
Trying to sum every X rows with no common group by parent

declare @temp table(Row_ID int, amt DECIMAL(18,2))
insert into @temp values(1, 20)
insert into @temp values(2, 24)
insert into @temp values(3, 20)
insert into @temp values(4, 15)
insert into @temp values(5, 15)

If X rows is 2 then desired result is to sum ever 2 rows and place total next to the rows. So first 2 rows total to 44 , next 2 rows to 35 , 1 remaining so total  15

Row_ID  amt     tot
1       20.00    44.00
2       24.00    44.00
3       20.00    35.00
4       15.00    35.00
5       15.00    15.00

If X rows is 3 then desired result

Row_ID  amt     tot
1       20.00    64.00
2       24.00    64.00
3       20.00    64.00
4       15.00    30.00
5       15.00    30.00

If X rows is 1 then desired result

Row_ID  amt     tot
1       20.00    20.00
2       24.00    24.00
3       20.00    20.00
4       15.00    15.00
5       15.00    15.00


texts: How can to make a new user see table in existing schema

texts:
I'm new into Oracle so this question might be dumb. I created new user and now I would like him to see table (or even better all tables by one command) in already existing schema (hr schema in this case). Is it even possible?

From my understanding databases store data and Schemas are equal to Users, so can two users see the same data stored in database, like in my case?


texts: (MySQL) How to change the results of a view according to the user that's viewing it?

texts:
I have a table, named servers, with a single column id.

This table is accessed by various machines each with a different username for the database.

I want to make it so each server does not see all the information on this table, but only a subset I designate to it in order to partition the workload among the app servers.

My original idea is to rename the table (say, to "servers_table"), add another "user" column to it, and create a view (with the original name) that shows the results based on the username currently connected to the server.

My table currently looks like this:

id  user
 1  server1
 2  server1
 3  server2
 4  server1
 5  server2

My attempt was (as "server1"):

CREATE VIEW server AS
SELECT id
FROM server_orig
WHERE usuario=SUBSTRING_INDEX(CURRENT_USER(),'@',1);

This, however, does not work, since CURRENT USER() is evaluated at CREATE VIEW time, not at SELECT time.

Therefore, running:

mysql -userver1 database -e "select * from server;"

yields:

+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+

BUT so does running it as server2..

I cannot (since the program is closed source) change the original select statement (and that would also incur eventually changing the insert/update statements too).

Is there a way this can be accomplished purely on the MySQL side?


texts: How to check if the SYSTEM_VERSIONING for a table is ON?

texts:
I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :

CREATE TABLE EmpSalary
(
     EmpID int NOT NULL PRIMARY KEY CLUSTERED
   , SalaryAmt decimal (10,2) NULL
   , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT)
)
WITH (SYSTEM_VERSIONING = ON);

Also to deactivate this, it is only necessary to ALTER the table:

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );

My question is how to check if the SYSTEM_VERSIONING for a table is ON and then ALTER the table?


texts: Problem with LISTEN - NOTIFY mechanism in PostgreSQL

texts:
I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism.
I open two consoles and connect to my database using psql client:

psql -U postgres test_db

In the first console I subscribe to some notification:

test_db=# listen event;
LISTEN

In the second console I'm trying to send a notification:

test_db=# notify event;
NOTIFY

And nothing happens in the first console. But if I send a notification from console 1, I get it and also I get all previous notifications from console 2.

I use PostgreSQL 9.2.1 under CentOS 6.5 x64.


texts: DBCC CheckDB causing high values for resource_semaphore wait type

texts:
On one of our production server, where SQL Server 2008R2 (Service Pack 2) is installed with 24 GB memory and 24 CPU cores grouped into two NUMA nodes, we are facing high counts for resource_semaphore waits WHEN we execute DBCC CheckDB for one of our database which is 125 GB in size.
Instance is up for last 6 months and working fine, but this start suddenly while no size change in database and no configuration changes performed.
While
Hard drives are performing up to the mark.
BUT
Strange thing i found is "ideal_memory_kb" values i.e. 5299767576 (5TB) for DBCC Check DB Session

select top 5 ideal_memory_kb,* from sys.dm_exec_query_memory_grants

Is it TempDB issue ?
Is there anything wrong on memory side?


texts: We are using MYSQL INNODB,After inserted one record in table,Locked database operations resulted in proxy and application threads to increase that ultimately caused the proxy server to not accept connections.I would like to know the root cause for this tables locking?


texts: Is there a way I can use currval() without selecting it more than once?

texts:
I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record):

Requests table has a serial column named requestid.

insert into testschema.requests (chid) values
(1);

I want to use select currval() multiple times without having to use SELECT for each line:

insert into testschema.requestinfo (requestid, reportid, amount) values
((select currval('testschema.requests_requestid_seq')), 10, 40),
((select currval('testschema.requests_requestid_seq')), 15, 29),
((select currval('testschema.requests_requestid_seq')), 40, 4);

I've tried placing the select on its own line with no success:

SELECT currval('testschema.requests_requestid_seq') as reqid;
INSERT INTO testschema.requestinfo(requestid, reportid) VALUES
 (reqid, 2);

Bottom line: I want to refer to the currval without having to use SELECT currval() for each line.


texts: changing tablespace seems to waste space

texts:
I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month.

I have two main tablespaces 'fastspace' on an SSD drive, and 'slowspace' on a traditional magnetic hard disk.

As data becomes older (and less relevant) I move the partitions from fastspace to slowspace using

ALTER TABLE..... SET TABLESPACE slowspace

I've started checking available disk space (at an OS level) before and after changing the tablespace, and have been surprised to notice that the destination drive (slowspace) is losing a lot more free space than the source drive (fastspace) is gaining.

A typical table might contain a 100 million rows of data for a month occupying several Gbytes of space. Changing the tablespace might free up x Gbytes of space from fastspace, whilst the destination tablespace slowspace will lose 2x Gbytes of space. (I'm moving both the table, and the indexes at the same time)

Why the difference, and what can be done to improve the situation?

Changing the tablespace is a relatively quick process (time measured in minutes) but doing another VACUUM after moving the partition to slowspace is very slow (time measured in hours). This is doubly frustrating because I do my best to ensure that performance intensive operations (like a VACUUM) are done on my fastest disk. To move the data to slower disk, and then be immediately forced to start a very slow operation to regain lost space is very annoying!.

Update in response to comments...
Not sure it's a block size issue. Both my discs have 512 bytes/sector, and the postgres page size is (I think) set per database, not per tablespace)

Both disks are NTFS. According to system information/components/Storage/Disks the 'fastspace' disk looks like this....

Description Disk drive
Manufacturer    (Standard disk drives)
Model   BIWIN SSD
Bytes/Sector    512
Media Loaded    Yes
Media Type  Fixed hard disk
Partitions  4
SCSI Bus    0
SCSI Logical Unit   0
SCSI Port   0
SCSI Target ID  0
Sectors/Track   63
Size    238.47 GB (256,052,966,400 bytes)
Total Cylinders 31,130
Total Sectors   500,103,450
Total Tracks    7,938,150
Tracks/Cylinder 255
Partition   Disk #0, Partition #0
Partition Size  260.00 MB (272,629,760 bytes)
Partition Starting Offset   1,048,576 bytes
Partition   Disk #0, Partition #1
Partition Size  236.67 GB (254,119,553,536 bytes)
Partition Starting Offset   407,896,064 bytes
Partition   Disk #0, Partition #2
Partition Size  960.00 MB (1,006,632,960 bytes)
Partition Starting Offset   254,528,192,512 bytes
Partition   Disk #0, Partition #3
Partition Size  500.00 MB (524,288,000 bytes)
Partition Starting Offset   255,535,874,048 bytes

And the 'slowspace' disk like this.....

Description Disk drive
Manufacturer    (Standard disk drives)
Model   TOSHIBA MQ01ABD100
Bytes/Sector    512
Media Loaded    Yes
Media Type  Fixed hard disk
Partitions  1
SCSI Bus    2
SCSI Logical Unit   0
SCSI Port   0
SCSI Target ID  0
Sectors/Track   63
Size    931.51 GB (1,000,202,273,280 bytes)
Total Cylinders 121,601
Total Sectors   1,953,520,065
Total Tracks    31,008,255
Tracks/Cylinder 255
Partition   Disk #1, Partition #0
Partition Size  931.39 GB (1,000,068,874,240 bytes)
Partition Starting Offset   135,266,304 bytes

I'm using a c# app to monitor disk space before/after the move using DriveInfo.AvailableFreeSpace immediately before and after using the ALTER TABLE ... SET TABLESPACE command.


texts: DB design to avoid versioning inconsistencies between the application layer and the database layer

texts:
Usually there is a 1-1 mapping between an object in the application layer and a table in the database layer.
This coupling leads to complexities whenever there is a database schema change and the application version may not be compatible with the current database version that it is talking to.
I was reading that there is an approach of database design that is driven by metadata. Something like the fields available for an object is found from a table and then a query is done. So some form of direction in order to avoid versioning problems.
Is this approach used in general and how exactly is it implemented? Also are there are other approaches to deal with this issue?


texts: SQL Server Management Studio 2018 Will Not Start

texts:
I have SSMS 2018 installed on Win10 and it keeps on refusing to start. I click the icon, I hear a windows audible error but no error message shows. I had the same issue before so I uninstalled and reinstalled SSMS. The problem is resolved for maybe a day (or possibly a few restarts) but then reappears. I had enterprise and developer edition of SQL server installed so uninstalled enterprise as do not need it.

I stumbled across one nugget of information. If I do a net start mssqlserver from the cmd prompt I see "system error 5 has occurred Access is denied."

All the MSSQL services seem to log on as Local System - I had read that the wrong user here could cause the issue


texts: Sql service account password change for environment on Availability groups 2012

texts:
My current environment is on Availability groups sql 2012 with 1 Synchronous and 1 Asynchronous replica. Soon we need to change the sql service account passwords.

Can any one suggest to me do I need to take any extra step(s) while changing the passwords? so that it will not disturb any existing settings or configurations?

Hope below steps are correct:
1. Changing the sql service account password on primary server, then secondary servers and restarting them
2. Ensure the data synchronization is properly running.


texts: Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

texts: We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?


texts: SQL Server Management Studio times out connecting to a remote SQL Server engine

texts:
I've done this at least a hundred times.

Install SQL Server on a VPS
Install it as the default instance
Only install the engine, not SSMS
Install Express edition
Go to SQL Server Configuration Manager
Enable TCP/IP
Change port from 1433 to another port
Open that port in Windows Firewall as Inbound Rule
Test port using Telnet from another connected machine
Install SSMS on another machine
Connect with IP,Port format

And one hundred times it worked. Now I'm stuck. I've done all the steps, and client can't connect to the engine. It simply times out. Here's what I've done:

Increasing time-out in connection window, but getting this error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)
Connecting to the engine, from within the VPS, using a connection.udl file, and it works just fine
Making sure that the engine is truly the default instance, using . in connection.udl and it's confirmed
Enabling ping following this link. Ping was not enabled by default, but for other VPS instances it was working. I compared their firewalls and their rules were just like each other. Yet this one didn't have ping. I enabled ping and still getting time-out
Installing SSMS on VPS to test any special thing that I'm not aware of, worked. Though SSMS on remote machine is 12.0.5000.0 and on VPS is 12.0.2000.8
Checking the software that I downloaded. I only downloaded the Express 64BIT\SQLEXPR_x64_ENU.exe from this page, and installed all features of it.

Now I'm totally stuck. I have no idea on what to do next. What other things should I test?


texts: SQL database scripts in EXE or DLL format

texts:
I work with SQL Server 2016 and I will need to send the generated scripts in some releases to our client, so they can install the package in UAT and Production.
I would like to send the scripts in DLL or EXE format, so they can't see the content. Is it possible?
Do I need some third party applications? If yes, which ones?


texts: Unattended install to listen on specified interface

texts:
I'm looking to install several versions of SQL Server side-by-side to run simultaneously. I'd like to have them listen on different IP addresses.

They will be running on Windows Server 2019 Core.

I currently install using something like this:

Setup.exe /qs /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SAPWD="MYSECRETPASSWORD" /SQLSVCINSTANTFILEINIT="True" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL

Am I right that there are no switches on Setup.exe that specify the IP address to listen to? If not what is the simplest way of configuring this from the command line after Setup.exe has run?


texts: Make 3 separate tables, or 1 combined table for similar data?

texts:
When designing a database's structure, if we have multiple tables with similar data, should these tables be combined, with an added ENUM field to denote the table type; or should 3 separate tables be created?

E.g.

VAT (sales tax) rates
National Minimum Wage rates
Currency exchange rates

The structure of the tables would be:

|============= VAT rates =============|
| id | rate |    from    |     to     |
|----|------|------------|------------|
| 1  | 17.5 | 1991-04-01 | 2008-11-30 |
| 2  | 15.0 | 2008-12-01 | 2009-12-31 |
| 3  | 17.5 | 2010-01-01 | 2011-01-03 |
| 4  | 20.0 | 2011-01-04 |    NULL    |
|=====================================|

All 3 tables are frequently referenced by queries.


texts: How to change aggregate function without duplicating SQL (by using SQL)

texts:
In SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a parameter that specifies which aggregation function to use to describe the groupings in a way that does not risk SQL injection and takes advantage of compilation (it is a heavy stored procedure).

My thoughts are to use a collection of queries that summarize the data's groupings on a particular aggregate function. (e.g. agg.DataMin, agg.DataMedian, agg.DataWeightedAverage, and so on).  Then use these with the parameter in a CTE

WITH AggData AS
(
   SELECT * FROM agg.DataMin WHERE @AggFunction = 1
   UNION ALL
   SELECT * FROM agg.DataMedian WHERE @AggFunction = 2
   UNION ALL
   SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3
)
SELECT ...

My concerns are query performance and industry best practice. The data table is of a reasonable size (2+ Gig). I will have to add many aggregate queries with some being inline table-valued functions for some leave-out aggregations.

In the above, will the queries/table-valued functions only execute when the @AggFunction matches the WHERE condition or will they all execute and filter after the results are returned? If the latter, is there a method to short-circuit the evaluation of the unneeded queries at run-time?  Also, is there some standard method to perform this in SQL that I have overlooked?


texts:
Please have a look at below events

1) create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ...

2) create EVENT Test2 ON SCHEDULE EVERY 20 SECOND STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO ...

I expect event Test1 to run one time after 20 seconds but it never runs.
Event Test2 is working fine.

Any idea? Thanks.

Ok sorry it is the alter that is not working

At first I did

CREATE EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

Then shortly after I did

ALTER EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO

I expected event Test1 to run again in another 20 secs but it didn't.


texts: What methodology is being used to poll SQL instances when using sqlcmd with the -L switch?

texts: I've discovered that issuing sqlcmd -L from the console of a SQL host in my network only reveals other instances on the same subnet. It used to reveal all instances on the network so I was wondering if an O/S or network firewall rule is blocking polling attempts outside the subnet. What firewall rule can I asked my network engineers to establish to allow polling for instances beyond the immediate subnet?


texts: Is searching an alternate index on a partitioned myISAM table concurrent or serial?

texts:
I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since the alternate key does not include the value used in the partition selection function, selecting on the alternate index can't use "partition pruning". Therefore, a select for an alternate key must involve searches on all 30 partitions. This much makes sense.

I was recently told that such a search is carried out serially -- on one partition after another. I find this hard to believe, since it sounds like something that could easily be done concurrently. Can anyone confirm this behavior?

mySQL 5.6.14


texts:
In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is:

CREATE TABLE TagValue
(
     tag VARCHAR(64),
     value BLOB,
     type INTEGER,
     PRIMARY KEY(tag)
);

Here in the 'type' column I put the type of data contained in value.

Is there a better solution?


texts: Are there major drawbacks/risk of using oracle blob to store files

texts:
Recently I was asked to do production support on an existing software.

In summary, this system is a web service that allows you to upload a file and provides you with an identifier so that you can ask for your file later.

My concern is that the files are stored as BLOB in an Oracle database.

For the moment, the system is stable, but in the near future our business wants to upload 100.000 to 200.000 PDF per year (10 to 50 Mb each).

When it comes to the Database

Should I just care about the disk space?
Are there other aspects I should worry about?
Or should I really try to switch to file system storage?

A few additional information:

There is no need for transactions.
All files are unrelated.
Files will never be updated.
Files will stay there "forever" (5 to 10 years).
The software needs to be updated for other reasons anyway.


texts: Correct way to design a shopping cart websites database

texts:
I am in a web applications course. My instructor really didn't like the layout of my database, saying it was incorrect in pretty much every way. Fast forward 2 months when I'm remaking the website in a new language. I can't for the life of me see what's wrong. What is the correct way of making this layout? I have to use these and only these tables.

regUsers:
un
addr
PK uID
email
etc

Links to orders on mutual PK uID

orders:
orderID
pk uID

Links to orderDetail on orderID

orderDetail:
PK orderID
total
items (list)
date
address

Each item in items links to products. Not sure how to write a formal link between a list of Product IDs to the product table in MS SQL server 2008

product:
PK prodectNo
Pname
notes
price
imgpath
stock

Which links to productType on productNo PK

Product Type:
productNo
type


texts: SQL Server Services empty in SQL Server configuration

texts:
As I upgraded from Windows 8.1 to Windows 10, I noticed that the SQL Server Services in SQL Server Configuration Manager is empty. SQL Server still works perfectly well at the moment. SQL Server 2014 is installed on a local machine.

I have read blogs - https://www.mssqltips.com/sqlservertip/2492/why-is-sql-server-configuration-manager-missing-services/ and
http://www.sqlservercentral.com/Forums/Topic1469391-1292-1.aspx and still could not resolve the issue.

I backed up databases, uninstalled and re-installed and yet the node is still empty. What should I do?

Updated with screen shot

SQL Server Configuration Manager Screen


texts:
I need some help with a complex query. Table def:

                                        Table "public.agent_log"
     Column      |           Type           |                          Modifiers
-----------------+--------------------------+-------------------------------------------------------------
 class           | text                     |
 hostname        | text                     |
 ip_address      | text                     |
 promise_handle  | text                     |
 promiser        | text                     |
 promisee        | text                     |
 policy_server   | text                     |
 rowId           | integer                  | not null default nextval('"agent_log_rowId_seq"'::regclass)
 timestamp       | timestamp with time zone |
 promise_outcome | text                     |
Indexes:
    "primary_key" PRIMARY KEY, btree ("rowId")
    "client_by_timestamp" btree ("timestamp", class)

Each line in the table is a log. There will be many rows that are the same except for the timestamp. As the data ages, I want to cull the rows. I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group. Example:

2014-01-22T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-22T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 cpu_1 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 cpu_1 192.168.0.2 mars.example.com ...

I'm testing with this, note that I'm using select instead of delete for this test. I can't get the where correct and the few things I've tried resulted in a very long query. Any suggestions for improvement or alternate approaches welcome.

explain SELECT * FROM agent_log
WHERE
NOT IN (
   SELECT
          class,
          max(timestamp) as timestamp,
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
      FROM agent_log
      WHERE timestamp < now() - interval '7 days'
      GROUP BY
          class,
          DATE_TRUNC( 'day', timestamp),
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
)
AND timestamp < now() - interval '7 days'


texts:
I have set a Master-master replication with the second Master running as read-only. This master-master setup is on the same location. Now I will have to setup an DR for this production at a different location. What are the solutions I have? Is it possible to set another master-master setup at DR site where in the Master in DR acts as the slave of the active master of DC? Will that work? Any other inputs for DR solution would be appreciated.

Thanks!!


texts: SQL Server - How to know to which database user certificate is mapped to?

texts:
Inside a user database, I have two certificates, both are used for signing stored procedures

  First cert was created long time ago by someone
  Second I created myself for testing purposes

For second cert I also created database user from certificate

When I query sys.certificates, I can see column [sid]

For the second cert I can find this [sid] in the sys.database_principals and can see which database user second cert is mapped to (which is the user I created)

  When I do the same for first cert (created by someone long time ago),
  this cert's [sid] can't be found in the sys.database_principals

Question: how do I find out which database user first cert is mapped to ?


texts: How do I allow remote connection in SQL Server 2008 Express

texts: I've just installed SQL Server 2008 Express. But when I try to connect to database engine via SQL Server Management Studio I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server" error. I want to allow remote connections to the server but Surface area configuration is missing in 2008. If I cannot connect to the server instance then how am I supposed to configure this feature? Thanks ahead.


texts: How to start Oracle enterprise manager on Windows 64 bits

texts:
I have installed Oracle 11g on my pc. It has installed fine and everything went fine.

After restating pc, https://localhost:1158/em is not connecting.
The listener is ok and running fine.

How can I resolve this problem?

If I try to start the OracleDBConsoleorcl it pop up an error massage that the OracleDBConsoleorcl could not start on local windows


texts: Table size and table partitioning on a single drive server

texts:
Suppose I have a big table on a server with only one drive. The table may grow up to a half TB (hundred million rows). I think the purpose of the partitioning is mainly for maintenance purpose because of one drive.

At which size should the table be partitioned?


texts: What are Measures and Dimensions in Cubes

texts:
I'm very new to Microsoft Sql Server Business  Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If it's possible with images)?

thanks


texts: How should I save apache logs into a mysql table?

texts:
This is a php script to view apache logs.

I want to save apache logs into a mysql database.

Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP 'some pattern';

a) Should I use one table for storing all the urls every time they are accessed even if they repeat and then do the REGEXP search and apply the tag to all of them?

b) Or it would be better to save one table with unique urls, and a second table with the id of the url and the time accessed? Then the tagging will be applied this table that has less rows if the url repeats itself.

If option 'b' is better, what kind of index should I use for unique urls? varchar(4000) primary key? I was thinking about creating a md5 hash of the string of the url and use that as primary key because it will be shorter.

I ask this question because I want to know what would be best performance when:

Tagging many urls with regexp search
Importing thousands of urls into one table and make sure they are unique

Thanks!


texts: How to use an RSA public key as the primary key to a table in postgres

texts:
I have a data set that needs to be looked up by RSA public keys.  These keys are 392 text characters long.

What is the best way to make that a primary and/or unique key?

Should it be a BYTEA field?

Is it too long for a primary key?  Should I store a shorter hashed version of it to index on and filter by first before checking for an exact match?


texts: Calculate time difference between a datetime and the current date & time

texts: I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.


texts: How to create a relatively complex MySQL query

texts:
I'm not good with MySQL but have been trying to create a query that would join from three tables where:

Table 1 (macs) is like this:
ipAddr, vlanID, macAddress, interfaceName

Table 2 (network_devices) is like this:
network_device_id, nd_location, nd_name, nd_ip_address, etc

Table 3 (vlans) is like this:
vlan_id, vlan_name

And I got this query thats works:

SELECT
    m.vlanID
    , m.interfaceName
    , count(m.macAddress) as mac
    , n.nd_name
FROM macs m, network_devices n
WHERE
    n.nd_ip_address = m.ipAddr
GROUP BY
    n.nd_name
ORDER BY
    m.vlanID

Which provides the following:
vlanID, interfaceName, mac, nd_name

And works ok. I'm now trying to add to this result the vlan_name which is in the vlans table.

How can I get this or where can I find information that would help me with this?


texts: How can I make a select statement get blocked?

texts:
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning.  I prefer only InnoDB tables.

To prepare the test, I created a table

CREATE TABLE `test`.`client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

and inserted 1,000,000 rows into the table.

Now I will begin the test.

In MySQL client #1, I deleted all the rows:

mysql #1> delete from client;

While the delete is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=1;
+---------+------+
| id      | name |
+---------+------+
| 1       | joe  |
+---------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.

Next I will try another test.  I insert 1,000,000 rows into the table.

In MySQL client #1, I update all the rows:

mysql #1> update client set name='Bill';

While the update is still executing, in another MySQL client #2, I try to select a row.

mysql #2> select * from client where id=100;
+-----+------+
| id  | name |
+-----+------+
| 100 | joe  |
+-----+------+
1 row in set (0.00 sec)

mysql #2> select * from client where id=1000;
+------+------+
| id   | name |
+------+------+
| 1000 | joe  |
+------+------+
1 row in set (0.00 sec)

The result is displayed instantly, the select was not blocked.

So now my question, how can I demonstrate a SQL select statement getting blocked by another simple SQL delete or or update statement, using InnoDB tables?  Or does MySQL never have any blocking issues?

PS I am not trying to simulate two processes deadlocking each other, just one large update or insert blocking a select.


texts:
I have a sqlite database with around 12,000 rows, the relevant columns are width1, height1, width2 and height2

My dynamic input is an arbitrary width and height, and I want to find all rows where the difference between the width and height are smaller than a x

To make it more compleicated, I'm never sure if the width and height of the input are reversed, so I also have to flip the values and test input width against stored height, etc.

Here's the ugly query:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE ((ABS(width - width1) < 10 AND ABS(height - height1) < 10) OR
       (ABS(height - width1) < 10 AND ABS(width - height1) < 10)) OR
      ((ABS(width - width2) < 10 AND ABS(height - height2) < 10) OR
       (ABS(height - width2) < 10 AND ABS(width - height2) < 10));

I have created combined indexes for width1, width2, height1, height2 as well as each column individually, but the query is missing the indexes.


texts: Stored Procedure with HINT "WITH RECOMPILE" loses performance, solved with DBCC FLUSHPROCINDB

texts:
I have a Stored Procedure with the hint WITH RECOMPILE, so the Stored Procedure do not store in the cache a Query Plan for the Stored Procedure. The average time the Stored Procedure resolve the Query is between 1 and 3 second, but from time to time the Stored Procedures takes > 30 seconds and sometimes it gets timeout.
The strange thing here is that I run DBCC FLUSHPROCINDB and the Stored Procedure returns to its normal time (1 to 3 seconds).

Do you have any idea of what could be happening?


texts: It's possible to order alphabetically all columns in tables of a mysql db?

texts:
It's a large db and it's really messy, I would like to order all columns in all tables, without resorting to manual drag and drop...

EDIT
I know the order doesn't matter. It's a design think. It will be much easier to read and to find things if they are ordered.
,I have also many columns like "is_visible", "is_editable" etc... if they are all together it looks better.


texts: Does SQL Server Support Parallel Bulk Inserts From Multiple Connections?

texts:
Does SQL Server support parallel bulk inserts into the same database table?
Some clarifications for my particular use case:

The table has a clustered index composed of two distinct index keys, and two non-clustered indexes, each one consisting of a single index key.
Parallel here means from multiple, distinct connections.  Each connection is a unique sqlalchemy.orm.session object, and each Session is inserting 20k records of data at the same time using the Session.bulk_insert_mappings() method.

The specific questions I have:

I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?
We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?
Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?
Does Microsoft have any sort of documentation on this?


texts: SSMS 17.1 stopping autocomplete suggestion of variable types

texts:
I'm trying to figure out how to stop autocomplete suggestion of variable types while keeping the autocomplete suggestion for table and column names (so disabling autocomplete function is note the solution).

For example, if I type NVARCHAR, the autocomplete suggests nvarchar and once I continue typing, it automatically correct the word.

It is a convention of my company to write variable types uppercase and the autocomplete function transform them to lower case each time. I know i can hit "Esc" to avoid the correction, but do this each single time is something really annoying.

UPDATE:
Actually the same problems happens when tiping for example IS:

CASE WHEN colName IS NULL

becomes

CASE WHEN colName IS_CALLERSIGNED NULL

if i don't press esc


texts:
I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.

If I use joins in between, I don't know how to use Left, Right, etc. in the SQL statement from priority aspect. I mean is it organized with parentheses or something else?

Relations for example are like following:

My query is as following:

SELECT T1.col1, T2.col2, T3.col3, T4.col1 AS Expr1
FROM T1
INNER JOIN T1_T2 ON T1.id = T1_T2.T1_FK
INNER JOIN T2 ON T1_T2.T2_FK = T2.id
LEFT JOIN T2_T3 ON T2.id = T2_T3.T2_FK
INNER JOIN T3 ON T2_T3.T3_FK = T3.id
LEFT JOIN   T3_T4 ON T3.id = T3_T4.T3_FK
INNER JOIN T4 ON T3_T4.T4_FK = T4.id

How does SQL Server interpret the joins? Can one represent it like execution priorities with parentheses? I mean: ((T1 INNER Join T1_T2) INNER JOIN T2) LEFT JOIN T2_T3) ?


texts: What happens when an automatically generated UUID primary key collides in Postgres?

texts:
If I have a PostgreSQL table with a column defined as follows, where gen_random_uuid() is from extension pgcrypto.

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

Upon each insert where an UUID is not specified, a new UUID (v4) is automatically generated to be set as the primary key. But seeing as UUIDv4 are generated at random, it could (at an astronomically low chance) collide with an existing row's UUID. What happens in this scenario? Does the insert return with a duplicate key error, or is generation repeated until it finds a UUID that does not collide?


texts:
Hello, I have this simple diagram showing two tables, event table and guest table.

I created an event which has id of 1 and there are 3 guests for event id of 1. If I want to reuse the same guests for event id 2 and 3, how do I relate them using a foreign key? What if I want to remove Joe from the event 2, how do I do it so that it won't delete Joe from the table because Joe is needed for event 1? I am confused and I don't know if my question is clear enough. Do I need to add some columns to help this out?


texts: PostgreSQL + query planner + amount of entities

texts:
I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan.

I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL calculates the amount of entities taken from the database.

But how does the RDBMS determine which rows must be taken. For example, database calculated that the current query need 1000 entities. But which exactly? How is it determined?


texts: How to restore mdf file in sql server 2005?

texts:
I have a .MDF file and no .LDF files for a database created in SQL Server 2005. When I try to attach the .MDF file to a different SQL Server, I get the following error message.

I would like to accomplish any one of the following options:

Attach the database without data loss (unlikely but would save me some time).
Attach the database with data loss (whatever transactions were open are lost).
Recover the schema only (no data) from the .MDF file.

What SQL commands can I try to get my database going again?


texts: How to Log IP Address for Inbound Connections in MySQL

texts:
Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track down the offending app server.

I googled around, and checked documentation but most of the discussion surrounded using IP Addresses as data in tables.


texts: Which data type should I use to store a 11 digit number

texts:
Which data type I should use to store an 11 digit number? It must store '0' as the first digit.

For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows 2421402015.


texts: Speedup MYSQL(8.0.13) Restore from .sql file

texts:
I am receiving .SQL(500mb) backup file and restoring to MYSQL (8.0.13) database.
Currently, restore is taking 5 hrs to complete. I would like to speedup the process and started working on it.
Here are the things I tried

Changed innodb_flush_log_at_trx_commit=2 from 1.

Changed expire_log_days from 0 to 2. My log was filling up and storing file for everyday.

My .sql file already have Unique key,Foreign key checks to zero.AutoCommit=0 like below
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Here are my my.ini file settings
 max_connections=151
 table_open_cache=2000
tmp_table_size=172M
 thread_cache_size=10
myisam_max_sort_file_size=100G
 myisam_sort_buffer_size=334M
key_buffer_size=8M
 read_buffer_size=64K
 read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=2
 innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
 innodb_log_file_size=48M
Innodb_thread_concurrency=17
 innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
 innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
 innodb_open_files=300 i
nnodb_stats_on_
metadata=0
 innodb_file_per_table=1
flush_time=0
join_buffer_size=256K
 max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
 sort_buffer_size=256K
 table_definition_cache=1400

frustrating part is sometimes restore is stuck for hours and I have to kill the connection.
Please let me know what I am doing wrong here.
Appreciate your help.


texts: Would refreshing a materialized view in a transaction allow me to have test-only specific data on it?

texts:
I have created a postgresql database used in integration tests for my code.
Its schema is consisted with some materialized views and the code I want to test uses them, therefore I need once I place the test data to refresh them.

But because refreshing them makes the tests slow I need to run the tests in parallel with the materialized view to contain data required only for the test.

Hence, I thought the following approach:

START TRANSACTION

INSERT INTO students (name,class) values ('MIZUKI MINOHARA','A1'),('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

REFRESH MATERIALIZED VIEW sempai_list;
-- RUN MY TESTS

ROLLBACK

But would this approach make my tests look on a separate "version" of the materialized views. What I want to achieve is to have the tests look on different test-only data from the materialized view sempai_list.

Do I achieve that by using and rollback transactions?


texts:
We recently made some changes to our infrastructure and now I can't keep MySQL replication running.  The slave complains of a corrupt binlog and resetting it doesn't help.  I keep seeing entries like this in my master log:

BINLOG '
WFxKTRNJAAAAPwAAAKY/YwAAABsAAAAAAAEACHdlYmVkaTMwAA1QYXJ0bmVyQ29uZmlnAAQICA8P
BC0AYwAA
WFxKTRhJAAAAXAAAAAJAYwAAABsAAAAAAAEABP//8BoSAAAAAAAAggMAAAAAAAAJUEFDS0NPVU5U
Azc3MfAaEgAAAAAAAIIDAAAAAAAACVBBQ0tDT1VOVAM3NzI=
'/*!*/;

In the slave log, this just comes across as "Unknown Event" and the replication fails at that position.

Here is my my.cnf for the master:

[mysqld]
datadir=/data/mysql-data
socket=/tmp/mysql.sock
user=mysql
log-error=/var/log/mysqld.log

binlog-do-db    = db1
binlog-do-db    = db2
binlog-do-db    = db3
binlog-do-db    = db4
binlog_format   = 'MIXED'

log-bin = /data/mysql-binlogs/mysql-bin.log
server-id=73
report-host=thisserver.mydomain.com

thread_cache_size = 30
key_buffer_size = 700M
myisam_sort_buffer_size = 300M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M
innodb_data_home_dir            = /data/mysql-data/
innodb_data_file_path           = InnoDB:100M:autoextend
set-variable                    = innodb_buffer_pool_size=500M
set-variable                    = innodb_additional_mem_pool_size=10M
set-variable                    = max_connections=500
innodb_log_group_home_dir       = /data/mysql-data
set-variable                    = innodb_log_file_size=20M
set-variable                    = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit  = 1

Does anybody know what might be causing the gibberish in my master binlogs?


texts: How can I properly use a trigger inside of stored procedure to validate an email?

texts:
-- Create a trigger inside of stored procedure that will enforce that Faculty's Email has to be
-- used in a standard way.

USE College ;

DROP PROCEDURE IF EXISTS Faculty_Before_Insert;

DELIMITER $$

CREATE PROCEDURE Faculty_Before_Insert()
BEGIN

        CREATE TRIGGER Review_Email_Before
        BEFORE INSERT ON Faculty
        FOR EACH ROW

      BEGIN
            DECLARE Email varchar(255);

        IF Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'  THEN
          SIGNAL SQLSTATE  'HY000'
             SET MESSAGE_TEXT= 'This email doesn\'t match FirstLast@college.edu';
        END IF ;

      END;

END
$$

DELIMITER ;

INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID)
              VALUES('Stanley', 'Mike', 'bad@bad.bad', current_date(), 20000, 1);

SELECT
Faculty.LastName,
Faculty.FirstName,
Faculty.Email
 FROM  Faculty
 WHERE ID= last_insert_id();

I am working in MySQL Workbench 6.3 CE and trying to use a trigger inside of a stored procedure to see if it will detect if my email format is not in the right format I specified in the message text error, but I am having trouble because it says a trigger can't be created inside of stored procedure. How else can I accomplish what I need to accomplish. This is a BEFORE TRIGGER since it will check the email validation before any rows are inserted into my Faculty table. What I am I doing wrong? Here is my SQL code below:


texts: What is the proper way to validate that a many-to-many relationship will not surpass a certain limit?

texts:
In a Postgres DB, I have a relationship as follows:

A Group table has a column userEntranceLimits.
A UserEntrance table has a column userId and a column groupId.
The count of a UserEntrance's distinct users for a specific group may not exceed the group's userEntranceLimits.
Inside of a transaction (whose isolation level is REPEATABLE_READ) for creating a new UserEntrance, I check if the current count of UserEntrances for that group is >= the limit.
If it is not, I proceed to create a new UserEntrance.
In some rare occasions, a group appears with more UserEntrances than its limit.

I understand why this race condition exists, but I do not understand the proper way to prevent it from happening without using the SERIALIZABLE isolation level. We have many concurrent writes on this table, and when we tried to make the transaction SERIALIZABLE before, it did not go too well.
My idea right now is to do a SELECT ... FOR UPDATE and to count the number of occurrences on the application side, but that looks like a bad solution to me. What is the proper solution for this kind of problem?


texts: show variables command constantly running on user

texts:
I have a bit of an odd problem.  I have a number of application user accounts.  One account per application rather than one account per user.

I have one application that seems to be hammering the systems resources.  CPU usage is at 99% for MySQL from this single user.  If I kill the application then usage for the server goes back down to about 20%.

This user account appears to be continuously running the "show variables" command... as far as I can tell, there is absolutely no reference in the application code to "show variables"... though I'm not sure even that should slow down the machine that much... there are also a handful of queries that appear multiple times in the connections list.

So I have two questions/problems:

Is there any reason the "show variables" command would be running constantly like this?  Any other sql commands that would cause "show variables" to fire?

AND

Why would I be seeing so many queries getting duplicated for this user?  If I have it running from a single PC, that PC generates somewhere around 30-50 connections and many of them are for the same query.  So is it running the same query multiple times when it does this?

I'm trying to pinpoint the source of these oddities, so if anyone can point me in some general direction I'd appreciate it.


texts: Can anything reveal that 2 independant entries were created or updated at the same time?

texts:
Imaginary example, simplified to better explain the question.
Let's say I have a form with the following fields :

user email
who did you vote for in 2012
who did you vote for in 2016

When submitted, I fill the following PostgreSQL (v.11) DB tables :

"UserList" table

Serial (auto-increment INT)
Email (text)

"UserData" table

Unique_Random (INT)
Vote_2012 (text)
Vote_2016 (text)

The entries are (as far as I can tell) unlinkable across tables because there is no relation between the Serial and the Unique Random ints.

Threat model : attacker gains full control of the postgreSQL DB server (both hardware and software)

If I am correct, UserData entries are not inserted in any specific position when created, so it should not be possible to tell which was added last.

Is there anything else (logs, data position on disk, memory, ...) that can reveal which entries were created or updated at the same time (and thus are linked) ?

If so, what can be done to prevent this ?


texts: Products with different prices in an order

texts:
I have a "classic" database schema for order and products:

Orders
------
id
etc.

Products
--------
id
name
etc.

Order Products  --> Pivot table
--------------
id --> some people don't like this, nevermind.
order_id (FK)
product_id (FK)
etc.

Now I have to deal with prices inside orders. Two problems:

There are two currencies -dollar and euros- and price are not calculated. Let's say, sometimes a product costs $79 vs 89€, some other times $89 vs 99€
Some products can be bought in a bundle and their prices can vary. Buying only product "A" costs $4, and buying product "A" with product "B" costs $3 + $3 (product "B" costs $3 in the "A"+"B" bundle)

Is there any simple, elegant and readable way to deal with such a problem? Maybe some part of the solution could be done outside of database, not sure.


texts: Postgresql - How to Convert all Column Names to Lowercase

texts:
I have a travel survey with a series of tables (lots and lots of columns) that I exported from MS Access into a postgres database. I've search around a lot for an explanation of how to convert all the column names to lower case, but I haven't been able to successfully apply any of the solutions I've found. I'm relatively new to postgres and would appreciate some step by step explanation if possible.

This link appears to have code for accomplishing this task, but I run into errors when trying to run them in psql or in the pgadmin.

http://www.postgresonline.com/journal/archives/141-Lowercasing-table-and-column-names.html

Or there is this:

\o /tmp/go_to_lower
select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';
psql -U username database < /tmp/go_to_lower

it appears to run but then the database still has upper_case columns.

I'd really appreciate some help with this, sorry if this is something simple that I'm just missing.

Many thanks


texts: Dealing with different types in a fact table

texts:
I'm working with dimensional modelling for the first time, trying to build a data warehouse that pulls in data from an OLTP database, and I'm having some trouble figuring out what to do with this type of scenario. The database tracks donations into a foundation. I'd like to have a fact table such that the "grain" is a single donation, i.e. at the "transaction" level. Donations can come from different types of donors. Let's say for example they come from individuals and companies, although in practice there are actually four relevant categories. The source data has tables for each of these donor types--so there is an individuals table and a companies table. Individuals and companies have very different attributes, and a typical reporting requirement will be to look at aggregate donations for all types, and then do deeper dives into donations by donor type. My first thought was to build a schema like so

(the colour is meaningless btw, I just happened to take the screenshot when the highlighted table was selected)

Since companies and individuals have such different attributes, the idea was to keep them in their own tables and have them be dimensions. (Remember that there are actually 4+ types like this in the real case).

But this is apparently not a good way to go because that means that there will be lots of NULL foreign keys in the fact table. I have read that this is a no-no (although I'm not sure why).

Another option would be to combine the Companies and Users tables into one big Donors table, which might look like this

This eliminates the NULL foreign keys from the fact table at the expense of simplicity in the dimension table. The Donor dimension table is now very wide, and contains a lot of null attributes (again, remember that there are 4+ types of donors that would be represented, each with its own unique attributes).

Finally, there is the option of having a Donor dimension that references Company and Individual tables. Here's how that might look:

Now all the null foreign keys are in the donor dimension table. Obviously, however, this is no longer a star schema.

I've been searching everywhere I can think of to find some general principles for dealing with this type of situation, but I'm coming up short. I'm just wondering if there is a standard way to deal with this since it seems like it ought to be a standard problem in data warehousing.


texts: Duplicate row with Primary Key in PostgreSQL

texts:
Assume I have a table as follows named people, where id is a Primary Key:

+-----------+---------+---------+
|  id       |  fname  |  lname  |
| (integer) | (text)  | (text)  |
+===========+=========+=========+
|  1        | Daniel  | Edwards |
|  2        | Fred    | Holt    |
|  3        | Henry   | Smith   |
+-----------+---------+---------+

I'm trying to write a row duplication query which is robust enough to account for schema changes to the table. Any time I add a column to the table, I don't want to have to go back and modify the duplication query.

I know I can do this, which will duplicate record id 2 and give the duplicated record a new id:

INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;

However if I add an age column, I'll need to modify the query to also account for the age column.

Obviously I can't do the following, because it will also duplicate the primary key, resulting in a duplicate key value violates unique constraint -- And, I don't want them to share the same id anyway:

INSERT INTO people SELECT * FROM people WHERE id = 2

With that said, what would be a reasonable approach to solving this challenge? I would prefer to stay away from stored procedures, but I'm not 100% against them, I suppose ...


texts: Private access for a SP working on a table

texts:
I have a Stored Procedure that is dropping, recreating and then applying keys and indexes to a table, but the process gets interrupted by other database tasks and then fails to apply the require keys and indexes.

The table is considered the main table within the Db and gets accessed later on by many apps and website so it is crucial that the indexes get applied.

Are there any tools within SQL Server that will allow me to isolate access to the newly made table until the keys and indexes have been applied ?


texts: How much work actually happens on SSMS and how much on the server itself?

texts:
Suppose I run the following query:

declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END

Is the loop actually running on the client machine, and calling insert each time over the network, or is the loop running on the server and making only local calls?

On the same lines, is it better to run such queries by connecting to the server using SSMS, or by using RDP to connect to the server and using the local SSMS instance?


texts:
I have a production SQL dump file that I want to restore to my local psql db for debugging reasons.

I can't access psql via the terminal though pgAdmin3 works just fine. Here is the error I'm getting:

$ psql
psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

$ locate psql | grep /bin
/Library/PostgreSQL/9.0/bin/psql
/Library/PostgreSQL/versions/9.0.1/bin/psql
/opt/local/bin/psql83
/opt/local/lib/postgresql83/bin/psql
/usr/bin/psql

I tried using sudo, no change. Any Idea why I can't connect w terminal on the mac and then restore the sql db dump locally?

Thanks


texts:
What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?

I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon COMMIT anyway.

Are there others?

If it matters, we are thinking about making all foreign keys in a certain table family DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.

TL;DR: What do we have to be aware of when making foreign keys DEFERRABLE INITIALLY DEFERRED?


texts: MySQL explain query: Using temporary table vs more rows examined


texts: I/O Requests taking longer than 15 seconds, Flush Cache During Replication SQL-Server

texts:
Hello I'm primarily a developer but I am trying to troubleshoot our database that basically just locked up last week. I have been diving into this pretty heavily but I'm also a bit out of my league in determining the exact problem.

Facts:

SQL Server 2012 SP1 SQL Server VM
8 vcores 6 are used for I/O affinity <- If i turn them all on the machine almost comes to a complete stop during a push replication
SAN storage on I want to say 7,200 RPM disks
Hosted on an offsite location that should just have a switch in between the storage and the SAN
Throughput at the switch is anywhere between 200GB-300GB during replication

Clues so far:

During replication heavy times 11PM to 5AM we have multiple error messages of I/O requests taking longer than 15 seconds sometimes the count is 30K+ other times 1 but a pretty good average of I'd say around 5000. I/O Issue
During replication times we have multiple Flush cache messages rolling through. Not sure what this points to

Latency seems to be sky high I/O Issue
Latch_EX and CXPacket are the big offenders but still some IO waits

At this point I did some small perfmon test to check the IO because it still looking like there is an IO problem. Note: This was not during the replication and really a low load time. I'll have more stats tomorrow.
Avg. Disk sec/Transfer - .0004 Avg .065 Max

Summary:
I have all of this information and I'm still thinking that underlying storage is an issue. What else can I do to build that case or what should I explore more? CXPacket is a parallel problem and could be an issue that starts the whole I/O problem based on my understanding.

I'm planning on:

Checking the perfmon report tomorrow.
Update

Checking to see if file filters are a problem
Running diskspd to stress test the underlying storage
Getting the I/O affinity right and changing MAXDOP to something a little under 8 so that the server doesn't just die.
Figuring out if anything else is taxing the VMHost or VM during those times (Backups in particular)


texts: Not able to login in Oracle 11g console using sys/system@SID as sysdba

texts:
I am not able to login into Oracle 11g console using administrative account, when I tried it gives me error of wrong username and password. Then I tried from SQL plus, it got logged in without any problem.
I use PL/SQL developer also , on connecting the database using it I am receiving error of insufficient privileges.
Request you tell me the solution for this, as if SYS account is locked than SQL plus will also not work for it, which is not the case here.


texts: What's the best method to refresh only a few tables within a test database from production?

texts:
I have a very large production database and a very large test environment database in SQL Server 2008R2. Both databases have similar table structure but different users/logins/permissions/roles.

I need to refresh only a few tables in the test database periodically from production, about once a month.

The current way I am planning on doing this is

Use the BCP utility to take an export of the tables I need from Production.
Copy the bcp export file onto test server
Disable indexes and constraints on all tables I am refreshing in Test
Truncate the Test database tables
Load Data back into Test database tables using BCP.
rebuild indexes and re-enable constraints in Test

This all seems a little too complicated for such a small task. It also seems like it would generate a lot of redo (in the t-log) Is there a better way to do this?

Another way I thought of doing this is to restore a backup from Production onto the test environment--but the issue I have is that a full backup would be quite large and I don't need all tables to be refreshed, only a few--and also the users and security in the production database is different from test. That would be overwritten by the security settings in the production database if I restore the whole database.


texts: Error while deploying package to catalog

texts:
I am getting below error while deploying package to SSIS catalog on the last step, I am sysadmin and have access to the below views. Can someone help please. Let me know if you need more info. Any help is appreciated

  The SELECT permission was denied on the object 'database_principals',
  database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server,
  Error: 229)


texts: ALTER TABLE AUTO_INCREMENT number in a Trigger

texts:
I want to have my auto increment column to increment by step of 3 upon insert.

Can I do something like:

Alter table `mytable` auto_increment = (select max(id) from mytable) + 3;

in a Trigger?

I understand that MySQL has a AUTO_INCREMENT_INCREMENT setting, but since I am on a shared server, I cannot change it.


texts: Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

texts:
Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app.

I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (ed. fixed code after answer below)

do $$
declare
target text;
begin
    for target in
        select quote_ident(column_name)
        from information_schema.columns
        where table_name = 'cds' and data_type = 'character varying'
    loop
        RAISE NOTICE 'Calling trim(%)', target;
        execute 'update cds set '
            || quote_ident(target)
            || ' = trim('
            || quote_ident(target)
            || ')';
    end loop;
end
$$;

The select gives my a nice list of all the column names in my table and I was hoping to for-loop through all of those:

gakera=# select column_name from information_schema.columns
         where table_name = 'cds' and data_type = 'character varying';

 column_name
-------------
 userID
 junk1
 dates
 times
 junk2
 junk3
 servID
 junk4
 junk5

Sidetrack

Not the question, but just extra confusing.

The fun starts at \i ~/path/test.sql with this error:

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  function quote_ident(record) does not exist
LINE 2:    || quote_ident(target)
              ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

What's going on with quote_ident? I'm gonna try a sanity check, I copied this from http://www.postgresql.org/docs/9.3/static/functions-string.html

gakera=# select quote_ident('test');
 quote_ident
-------------
 test

Ok, insanity check then:

gakera=# select quote_indeed('test');
ERROR:  function quote_indeed(unknown) does not exist
LINE 1: select quote_indeed('test');
               ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

Ugh, whatever, I'll just remove the quote_identfunction from the offending lines (it didn't complain about the select quote_ident part so I keep that. Now I have:

    execute 'update cds set '
        || target
        || ' = trim('
        || target
        || ')';

Question

Here is where things start to get interesting. when I run this like before I get:

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  syntax error at or near "trim"
LINE 1: update cds set (anum) = trim((anum))
                                 ^
QUERY:  update cds set (anum) = trim((anum))
CONTEXT:  PL/pgSQL function inline_code_block line 11 at EXECUTE statement

Why is it adding () around anum? How can I get rid of them? The EXECUTE statement should read update cds set anum = trim(anum)


texts: Select rows from my table where a character column matches a pattern

texts:
I'm using Postgres 9.5. I have a name column of type "character varying".  I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data.

So I would want rows where the column looks like:

12 DAVE
1 abcDEF
12345 LAST

But not when it looks like:

MYCOLUMN
1TEXT
=END


texts: How to view contents of table in mysql-workbench

texts:
Hi everyone am fairly new to mysql, I made a java program that stores data in mysql database. I am using this tool called mysql-workbench that can create databases, tables and much more. What I wanted to do is to view all the contents of the table I just populated with data in my java program. I know that one can view all the contents of a table using some SQL scripts, however am wondering if mysql-workbench can display all the contents in the table without entering SELECT mysql script in a sort of tabular form or similar to how they display contents of table in phpMyAdmin.

The reason why I wanted to do this is that I have 2-3k entries in that table and using a console is kind of messy. Any help or suggestions on how to do this? Thanks in advance.


texts:
Why can't I spool the explain plan to a txt file?

SPOOL EXPLAIN_PLAN_1.txt

set heading off
set echo off
set pages 999
set long 90000

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));

SPOOL OFF;

Error that I see

SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Oracle Version: 11g


texts: Doubts about Converting MyISAM to InnoDB (master/slave and more)

texts:
I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process.

Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?
Do I have to stop the slave before the conversion?
I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?
I use only stored procedure to run queries. Is there something I need to change for the new engine?

If you have any other suggestions, feel free to share them with me.


texts: Separate table for 'scheduled payments' and 'completed payments'?

texts:
In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute them, and update that row to status completed.

I've kept wondering why they didn't have a separate 'completed transactions' table? Is that bad practice?


texts: Running queries/jobs via SMSS - SQL service and SQL agent service accounts (SQL Server)

texts:
If a command, for example the below command, is executed from SQL Server Management Studio while I am logged in the session with a windows authentication login, I understood that the access to the path 'C:\SQL2019\BookStore.bak'is done  via the sql service account (set in the SQL Server Configuration Manager) and not via my login. However, if an INSERT statement is done, is it done via my login in that case?

BACKUP DATABASE [BookStore] TO  DISK = N'C:\SQL2019\BookStore.bak'
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BookStore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Also, I have an agent job set and in the job history it states "Executed as user: sqlagent1" which is the  sql agent service account that I have set in the SQL Server Configuration Manager. The run as in the job step properties has been left empty. However, there is no login created for this account under the Logins in SQL Server. From where does this account get permission?


texts: Can I select data inserted in the same uncommited transaction?

texts:
Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. My concern is the behavior within a single transaction.

If I start a transaction, insert some data, am I going to be able to select them right after - still within the same, yet uncommitted, transaction?
If yes, can this behavior be changed in a similar way like the mentioned Transaction Isolation in the case of concurrent transactions?

To be specific, I'm targetting PostgreSQL 9.4.


texts: WHERE clause not preventing return of row but changing values for aggregate function (here: count)

texts:
I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion.

Consider this simple query

SELECT
   'something confidential'
WHERE FALSE;

The nonsense clause WHERE FALSE resembles here a very strict permission check.
That works as expected: Nothing is returned.

Now, imagine you add an additional column e.g. a count function.
This gives is this query:

SELECT
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

(The parameter of count could be anything. CURRENT_DATE is just a random example.)

Now, we get

  something confidential | 0

I guess, this is somehow on purpose, in the sense that SELECT count(CURRENT_DATE); returns 1 if the condition is truthy and 0 if the condition is falsy.

However, I consider this as problematic in situations where you are not aware of it.
So, my questions are

How come? What is the background of this behavior?
How can I make such a query return zero rows instead of one row with value 0 for count?
Is there a way directly in the query for preventing accidentally returning rows you don't want to return by adding aggregate functions? (I.e. apart from external tests etc.)

I'm using PostgreSQL 12.1, but the behavior is the same for older versions.


texts: Query hangs in preparing state and blocks others in waiting for table flush

texts:
In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries:

UPDATE products
SET visible = 1
WHERE merchant_id = in_merchant_id
    AND source_code IN (
        SELECT source_code
        FROM feeds_processing_data
        WHERE processing_id = in_processing_id
        )

It basically updates a specific "window" on the products table, turning visibiliy on or off based on some other info. That table is central to the webapp, searches from the frontend are run on in (either via FULLTEXT or by code lookups), and background processes update its contents from time to time.
I usually approach these issues by checking the query plan and verifying that proper indexes are there.

What puzzles me, though, is that listing sessions from information_schema.PROCESSLIST shows that the above query sits in the preparing state, while the other blocked queries are Waiting for table flush, something I have little to no knowledge about.

This is one of those blocked queries:

SELECT id,
    [...]
FROM (
    SELECT (MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE)) AS rank,
        p.*
    FROM products p
    WHERE visible = 1
        AND MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE) LIMIT 400
    ) ranked
ORDER BY final_price

At a first glance, I see no problems with that UPDATE, this is its EXPLAIN plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY products    range   uk_merchant_id_source_code  uk_merchant_id_source_code  4   const   3861    Using where
2   DEPENDENT SUBQUERY  feeds_processing_data   ref fk_processing,source_code   fk_processing   4   const   2   Using where

Indexes on the involved tables follow.

SHOW INDEXES FROM products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
products    0   PRIMARY 1   id  A   409324  NULL    NULL        BTREE
products    0   uk_merchant_id_source_code  1   merchant_id A   126 NULL    NULL        BTREE
products    0   uk_merchant_id_source_code  2   source_code A   409324  NULL    NULL        BTREE
products    1   visible 1   visible A   2   NULL    NULL        BTREE
products    1   gtin    1   gtin    A   409324  NULL    NULL    YES BTREE
products    1   industry_code   1   industry_code   A   409324  NULL    NULL    YES BTREE
products    1   name_description_manufacturer_name  1   name    NULL    409324  NULL    NULL    YES FULLTEXT
products    1   name_description_manufacturer_name  2   description NULL    409324  NULL    NULL    YES FULLTEXT
products    1   name_description_manufacturer_name  3   manufacturer_name   NULL    409324  NULL    NULL    YES FULLTEXT

SHOW INDEXES FROM feeds_processing_data:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
feeds_processing_data   0   PRIMARY 1   id  A   10336188    NULL    NULL        BTREE
feeds_processing_data   1   fk_processing   1   processing_id   A   2981    NULL    NULL        BTREE
feeds_processing_data   1   source_code 1   source_code A   136002  NULL    NULL    YES BTREE
feeds_processing_data   1   rejected    1   rejected    A   1   NULL    NULL        BTREE

The first question I can't address is: why is it taking so much to complete the prepare phase?
Then, how could I further inspect the issue? What informations am I missing here?