texts: How to grant permission to view database audit logs to non-sysadmin login

texts:
I have a non-sysadmin user which must be provided with an ability to view "server audit logs"

Tried to grant below permissions to this login / user:

grant ALTER ANY server AUDIT  to [login]
grant ALTER ANY database AUDIT  to [user]

But if he runs below, he is still getting error message:

select top 100 * from fn_get_audit_file(Path:\\...)

Msg 297, Level 16, State 1, Line 44
The user does not have permission to perform this action.

So, is there an ability to grant non-admin user permission to view audit logs ?

Thanks!


texts: Why does MySQL increment the auto_increment value when there's a constraint violation?

texts:
I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns.

I noticed that when I try to add a record that violates the constraint, the auto_increment value is still incremented. This seems weird to me, because it just means the value is getting higher while that isn't necessary.

I'm not asking for a way to work around this, but I'm just curious as to why this would be. Is this something that is useful in some cases?

I'm using Mysql 5.5.38-0ubuntu0.14.04.1.


texts: Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why?

texts:
Why am I getting 2 instead of 3 when I perform BIT_OR(2,1)?

SELECT UTL_RAW.CAST_TO_NUMBER (              --> decimal: 3? nope, 2
            UTL_RAW.BIT_OR(                  --> binary: 11? shouldn't it?
                UTL_RAW.CAST_FROM_NUMBER(2), --> binary: 10
                UTL_RAW.CAST_FROM_NUMBER(1)  --> binary:  1
            )
        )
FROM DUAL;


texts: How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model

texts:
Can someone use the entity relationships in this Logical Data Model (LDM):

to explain the entity relationships in this Physical Data Model (PDM):

and how to specify the foreign keys in the PDM?


texts: SSIS changes SQL Server source data type from date to WSTR

texts:
I want to work with a table from SQL Server in an SSIS data flow. One of the columns of the table is date type. When I import it to SSIS using OLE DB source component, SSIS changes the metadata of this column to WSTR.

Do you have any idea how to fix it? I think that it never happened before. I know I can force SSIS to change the data type in advanced editor, but shouldn't SSIS "know" that this is date type column and convert it into DT_DATE?

I use SQLOLEDB.1 as the provider. It's SQL Server 2012 and the data type of the column is just date.


texts: What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

texts:
We're using PostgreSQL v8.2.3.  Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we
don't use other features of pgpool like Replication, Load Balancing, etc.).

Recently, in our Production server there was a drastic and unexpected growth in
database disk space.  In just 2 days, database size has grown from 6 GB to
14 GB.

I then ran the following query to find the size of the top 20 biggest
relation in the database:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class
C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN
('pg_catalog') ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;

I didn't find any issues here.  Even I could say that the sum of
"total_size" of the above command is less than the size occupied by the
database itself.  I'm using the following command to find the size of
database:

select oid, datname, pg_database_size(datname) as actualsize,
pg_size_pretty(pg_database_size(datname)) as size from pg_database order by
datname;

I also used to physically check the database size occupied using the following
command:

du -sh /usr/local/pgsql/data/base/2663326

I then physically listed the file size in descending order from the location
"/usr/local/pgsql/data/base/2663326".  Here, "2663326" is the OID of my
database.

[root@dbserver 2663326]# ll -lhS |head -15
total 14G
-rw-------  1 postgres postgres  1.0G Sep  6 15:03 1508904
-rw-------  1 postgres postgres  1.0G Sep  2 21:16 1924478.10
-rw-------  1 postgres postgres  1.0G Sep  2 21:17 1924478.2
-rw-------  1 postgres postgres  1.0G Sep  2 21:19 1924478.3
-rw-------  1 postgres postgres  1.0G Sep  2 21:17 1924478.4
-rw-------  1 postgres postgres  1.0G Sep  2 21:18 1924478.5
-rw-------  1 postgres postgres  1.0G Sep  2 21:20 1924478.6
-rw-------  1 postgres postgres  1.0G Sep  2 21:20 1924478.7
-rw-------  1 postgres postgres  1.0G Sep  2 21:14 1924478.8
-rw-------  1 postgres postgres  1.0G Sep  2 21:19 1924478.9
-rw-------  1 postgres postgres  876M Sep  6 15:02 1508614
-rw-------  1 postgres postgres  615M Sep  6 15:03 1508904.1
-rw-------  1 postgres postgres  531M Sep  2 21:20 1924478.11
-rw-------  1 postgres postgres  235M Sep  6 15:02 1510463

Though these files are not human-readable, from whatever I was able to read
from the file, I found that the top 10 files created are related to a
particular complex application report.   In this complex report, we're
creating temporary table using CREATE TEMP TABLE MY_TEMP_TABLE(col1, col2,
...), 5 columns in this temp table are indexed and it's being heavily used.
Though temporary tables are automatically dropped at the end of a session,
I'm finding that the disk space occupied by these temporary tables are not
being freed-up.  As you can also see, some file names are numbered with
decimal places (1924478.2, 1924478.3, etc.) with a maximum file size of 1
GB.  Particularly, these type of files are related to this complex report
that makes use of temporary tables.

I can also confirm that my temporary tables are not getting listed from the
following query (which shows that as per system catalog tables, it's been dropped):

select pn.nspname, pc.relname, pc.relfilenode from pg_class pc, pg_namespace
pn where pc.relnamespace = pn.oid and pc.relname ilike 'my_temp_table';

NOTE: Auto vacuum daemon is already running in the server.  Even a manual
VACUUM FULL ANALYZE, followed by REINDEX command is not able to reclaim the
lost disk space.  Only when we exported and imported, the database size
comes back to the original 6 GB size.

So, based on my observations, it appears that at some point of time/context, for some unknown reasons, the disk
space occupied by the temporary table is not being released properly by PostgreSQL server.

What are all the reasons/possibilities that it may fail to free-up disk space
occupied by TEMPORARY table?   How do I fix/handle in this situation?


texts: How can i select columns from two tables using REF

texts:
I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects tables using references in the third table.

First Type Object looks like:

CREATE OR REPLACE TYPE Teacher_T AS OBJECT
(
  teacher_id VARCHAR2(10),
  teacher_name VARCHAR2(20),
  teacher_address VARCHAR2(10),
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2),
  MEMBER PROCEDURE Delete_Teacher
)
/

And its table:

CREATE TABLE Teacher OF Teacher_T
(
  teacher_id NOT NULL,
  PRIMARY KEY (teacher_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY Teacher_T AS
  MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                  new_teacher_name IN VARCHAR2,
                                  new_teacher_address IN VARCHAR2) IS
  BEGIN
    INSERT INTO Teacher
     VALUES (new_teacher_id, new_teacher_name,new_teacher_address);
  END Insert_Teacher;

  MEMBER PROCEDURE Delete_Teacher IS
  BEGIN
    DELETE FROM Teacher
     WHERE Teacher.teacher_id = self.teacher_id;
  END Delete_Teacher;
END;
/

Example to insert data:

declare
  tea Teacher_T;
begin
  tea := new Teacher_T('','','');
  tea.Insert_Teacher('1','Jhon tea1','Cty');
end;

Second Type Object looks like:

CREATE OR REPLACE TYPE School_T AS OBJECT
(
  sch_id VARCHAR2(10),
  sch_name VARCHAR2(20),
  sch_address VARCHAR2(30),
  sch_type VARCHAR2(15),

  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2),
  MEMBER PROCEDURE Delete_Sch
)
/

And its table:

CREATE TABLE School OF School_T(
  sch_id NOT NULL,
  sch_type CHECK (sch_type IN ('Primary', 'Secondary', 'TechC')),
  PRIMARY KEY (sch_id)
);

Procedures definition:

CREATE OR REPLACE TYPE BODY School_T AS
  MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                              new_sch_name IN VARCHAR2,
                              new_sch_address IN VARCHAR2,
                              new_sch_type IN VARCHAR2) IS
  BEGIN
    INSERT INTO School
    VALUES (new_sch_id, new_sch_name, new_sch_address, new_sch_type);
  END Insert_Sch;

  MEMBER PROCEDURE Delete_Sch IS
  BEGIN
    DELETE FROM Teach_In
    WHERE Teach_In.school IN (SELECT REF(a)
                                FROM School a
                               WHERE a.sch_id = self.sch_id);
    DELETE FROM School
    WHERE School.sch_id = self.sch_id;
  END Delete_Sch;
END;
/

Example to insert data:

declare
  sch School_T;
begin
  sch := new School_T('','','','');
  sch.Insert_Sch('2','schName','NYC','Secondary');
end;

Finally references table:

CREATE TABLE Teach_In
(
  teacher REF Teacher_T,
  school REF School_T
);

Procedure def:

CREATE OR REPLACE PROCEDURE Insert_Teach_In(new_teacher_id IN VARCHAR2,
                                            new_sch_id IN VARCHAR2)
AS
  teacher_temp REF Teacher_T;
  school_temp REF School_T;
BEGIN
  SELECT REF(a) INTO teacher_temp
    FROM Teacher a
   WHERE a.teacher_id = new_teacher_id;

  SELECT REF(b) INTO school_temp
    FROM School b
   WHERE b.sch_id = new_sch_id;

  INSERT INTO Teach_In
  VALUES (teacher_temp, school_temp);
END Insert_Teach_In;
/

Example to insert data:

begin
  Insert_Teach_In('1','2');
end;

So my goal from this example is to retrieve teacher_name,teacher_address,sch_name,sch_type,sch_address using Teach_In table(references table).


texts: Retrieve alert error description text via SQL Server Agent

texts:
We have configured Alerts for the major severities and error codes on our SQL Server instances. Each alert then runs the same SQL Server Agent job, to trigger an alert in our monitoring system (for SMS messaging).

It would be useful to be able to retrieve the alert description text (see "This was just a test!" in the example below) from somewhere, to feed through to our monitoring system.

DATE/TIME:   27/09/2018 16:08:19
DESCRIPTION: Error: 50000 Severity: 20 State: 1 This was just a test!
COMMENT:     (None)
JOB RUN:     SystemAlerts

Is this text stored anywhere in the msdb database? Or is it not persisted like SQL Server Agent job history?


texts: Getting error "String or binary data would be truncated" even for 'text' data type

texts:
I have created a linked server (PostgreSQL). When I try to import data from
SQL Server to linked server I get the following error:

  String or binary data would be truncated

Even though I am familiar with this error, I have never faced it for the text data type. There is only text datatype in my table.

I would like to know is there are any limitation in linked server?

I have created a test table with a text column. When I try to insert from my linked server I can't insert more than 4000 characters. For testing purpose only I did this but actually I need to import data from SQL to p-sql.

Created following table in p-sql using pg admin

create table test_sql ( col text )

When I try to then inserted values

insert into linkedserver.database.schema.table (col)
values ('4001 char')

but it fails to insert.

I have successfully created linked server with below query:

EXEC master.dbo.sp_addlinkedserver
@server = N'Test_server',
@srvproduct=N'PostgreSQL Unicode(x64)',
@provider=N'MSDASQL',
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=######;database=test_db;pwd=password;SSLmode=disable;PORT=5432'


texts:
Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an additional 300+ temp tables in the temp DB. I also noticed that SQL Server's memory growth over these 3 hours was huge - 15 GB utilized after 3 hrs.

There were only 3-4 users testing on the test system and about 5 connections open to the application when I ran a SP_WHO.

What can be causing this? I did a query on the objects in the temp DB and all the names are similar to this #XXXXXXXXX, I read somewhere that these are from the Table Variable (Declare @myTable...)

Is there any way to check why we have a growth in the number of temp tables & what is causing it? Any help at this stage would be greatly appreciated.

I am going to be installing the service packs tomorrow for 2008, hopefully that solves the problem.


texts:
I have the following MySql query:
SELECT
    E.stock_code,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            "stock_code",
            P.stock_code,
            "PROM_NO",
            P.PROM_NO
        )
    ) AS local_prom
FROM
    E
LEFT JOIN
    p
ON
    E.stock_code = P.stock_code

I have more than 27000 rows in table E and more than 6000 rows in table P.
I would like to have as result all the E.stock_code/s with all his columns from table E and the P.stock_code and P.PROM_NO columns from table P for each E.stock_code as Json object
When I run it I get back only one row back from E table and all the data from table P in local_prom column.
Edited

What I'm doing wrong here?
Thanks in advance.


texts: Query plan do not get invalidated after update statistics

texts:
I'm running some tests to find out when query plans get invalidated after an update statistics. The machine I'm using for testing is a SQL Server Developer 2016 SP1 CU7.

I found an article on BrentOzar.com how I can trace the recompiles but I never get a recompile.
Auto Update Statistics and Auto Create Statistics are both enabled

This is my test:

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField)
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

As I understand it, the plan should get invalidated when you call UPDATE STATISTICS WITH FULLSCAN and there are rows changed.
Or am I missing something?


texts: grant select in just one DMV instead of grant VIEW SERVER STATE

texts: Is there some way to grant select in just one sys.table.. for example select in sys.dm_hadr_availability_group_states without to grant VIEW SERVER STATE?


texts: Geo-clustering using SQL Server 2012 Enterprise with AlwaysOn

texts: We are trying to take our current SQL 2012 Enterprise edition 3 node cluster (OS 2012) and add a 4th node to the cluster that will be at another location.  And everything I find is telling me to use SAN replication to handle the data movement from local cluster to geo-cluster node.  WHy?  Doesn't AlwaysOn handle that data movement piece?  Any insight would be appreciated.  Thanks.


texts: How to reset slave without over write of master dump in mysql

texts:
My master having position is
File                 Position

mysql-bin.000046   430674598
I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both having different log position .But I am  afraid of the starting slave.the dump which i stored in master will rewrite on my salve.
slave position is
Relay_Master_Log_File:
mysql-bin.000040
Exec_Master_Log_Pos: 933087278
Relay_Log_Space: 767900934
Please suggest me way to configure it properly.
how to resync my slave ?**


texts: Why this query returns rows that I don't want to be there

texts:
I'm trying to running this query:

SELECT *
FROM
    (`default_users`)
    LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id`
WHERE
    `default_users`.`group_id` = '3'
    OR `default_users`.`group_id` = '4'
    OR `default_users`.`group_id` = '5'
    AND `default_users`.`email` LIKE '%polar%'
    OR `default_users`.`username` LIKE '%polar%'
ORDER BY `default_users`.`id` DESC

But get unwanted results and don't know the cause and by unwanted results means webmaster which I think isn like %polar%. Here are some sample data for testing http://pastebin.com/Fyx0TcGY, any advice?


texts: Postgresql script adding parameter to \copy command

texts:
I am running on a postgresql server version 9.6, and I am using psql.

I am trying to write a script that automatically imports a bunch of CSV files. The requirements are that it it is a single SQL script that a user can run that does not have access  to the server itself.

This means that the copy command results in a permissions denied error. So I have to use the \copy command.

This all works fine and dandy, but the script now contains a lot of hard coded directory names.

This is the basic code that I am trying:

\set test '\'' :in_dir /parcel.csv '\'';
select :test ;
\copy temp_table from :test with delimiter ',' quote '"' csv;

the result is a

  psql:d:/test.sql:20: :test: No such file or directory

I've tried moving it to a function with an 'execute format' statement, but \copy does not work there.
A regular copy results in a permission denied.

Is there any way to make the directory parameterized?


texts:
I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. I then wrote the join statement:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2
    ON db1.dbo.table1.datecolumn = db2.dbo.table2.datecolumn

And the query returns the date column from table1, but the column from table2 is populated with NULL values only. I am not sure what I am doing wrong. I tried CAST instead of CONVERT, with no luck. Is there something happening with the conversion? Any help is appreciated.


texts: Where to put a database in case of IP failover?

texts:
I understand that to achieve some level of availability of an online application it is possible to organize an environment with a redundancy using an IP failover mechanism and switching between several identical servers in the event a problem arises in any one of them.

But what about with databases? Should the database be on each server with some sort of replication data between them or should it sit on its own dedicated server?

What approach usually is used in SQL Server 2008 R2 or SQL Server 2008 R2 Express?


texts: Cannot create foreign key on 2 VARCHAR(45) columns in MySQL Workbench 6.1

texts:
I'm trying to create a foreign key. The columns in both tables are VARCHAR(45) and neither is a PK, however I keep getting this error message:

It is saying the PK of reference table is INT, which is true, but I am not referencing this column, I am referencing another column in this table that is VARCHAR(45).

I have already made to FK in this same table where both columns are VARCHAR(45), which is why I am wondering what I am doing wrong this time.

UPDATE:

Just figured it out.

The reference table column needed to be a Unique Index, then the foreign key worked.

Not a DB guy, but that sounds like a rookie mistake on my part.


texts: Extremely bloated transaction log during delete

texts:
Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!.
I have a 27GB database with around 30GB log. The table that is beeing deleted from is around 15GB in size, with 20 million records in it. The table has 20 columns only with bigint and int data types.
The delete operation is deleting 18 million duplicate records from the table in question. I have switched selected for the delete to make sure the record count matches and it does.
The log grows to about 110GB before I run out of disk space and it´s forced to rollback.
There are no triggers on the table but 16 Non-clustered indexes, no clustered index. If I disable all indexes before running the operation it finishes without ever growing the log from the original size.
So my question is, am I logging every delete from every index when deleting from the table? If so, is that normal behaviour or could the reason be because of missing clustered index?


texts: Oracle SUM - ORA-00937: not a single-group group function

texts:
For every journey want to sum them to work out the distance between.

SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID;

Tables are as followed:

CREATE TABLE Journey_Stages(Journey_No integer REFERENCES
Journey(Journey_No),Stage_ID integer REFERENCES Stages(Stage_ID));

CREATE TABLE Stages(Stage_ID integer PRIMARY KEY, Start_Station
integer REFERENCES Stations(Station_ID), End_Station integer
REFERENCES Stations(Station_ID));

CREATE TABLE Journey(Journey_No integer PRIMARY KEY, Train_No integer
REFERENCES Train(Train_No));

However I am getting the following error:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:
*Action: Error at Line: 273 Column: 26

I am certain this is something simple to resolve.


texts: Get the last row that was left outside of a timeframed SQL query

texts:
Is there a way to write a SQL query with a timeframe condition to include the latest row that is outside of the timeframe (besides solutions like counting the size of the result set and querying for the size+1, etc.) ?

Lets say I have a table A, which holds timestamped value changes.
I want to query for all the changes in the last 24 hours (assume that the time when the query was ran on 8/8/19 00:00) - how do I include the last row that isn't included in 24-hours interval, i.e., row #2 (assuming I don't know when it occurred):

CREATE TABLE A(`timeframe` datetime, `value` int);

INSERT INTO A
    (`timeframe`, `value`)
VALUES
    ('2019-08-06 15:00:00', 10),
    ('2019-08-06 23:00:00', 20),
    ('2019-08-07 14:00:00', 50),
    ('2019-08-07 16:00:00', 10),
    ('2019-08-07 22:35:00', 30),
    ('2019-08-07 23:00:00', 40);

| timestamp        | value |
|------------------|-------|
| 2019-08-06 15:00 | 10    |
| 2019-08-06 23:00 | 20    |
| 2019-08-07 14:00 | 50    |
| 2019-08-07 16:00 | 10    |
| 2019-08-07 22:35 | 30    |
| 2019-08-07 23:00 | 40    |

SELECT value,time FROM A WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY)

The result set should include the value changes in last day (rows #3-#6) and the latest row outside the timeframe - row #2.


texts:
I have table with observations of objects moving along edges in a graph, this table has the following form:

PK | TIMESTAMP | object_id | from_id | to_id

where object_id is the id of some object and from_id and to_id are vertices.

Since the movements are observed at a high frequency the tuple

(object_id, from_id, to_id)

is repeated often for different PK and TIMESTAMPS. I'm interested in all the separate edge traversals, so if an object with id 1 moves from vertex 1 to 2, from 2 to 1 and from 1 to 2 I want to have a result:

object_id | from_id | to_id
        1 |       1 |     2
        1 |       2 |     1
        1 |       1 |     2

My question: how to write this query?


texts: Select pseudorandom row from a very small partial index in Postgres?

texts: I have a huge table, with a tiny partial index on <0.1% of the rows satisfying a certain condition (one column is null). I would like to pseudorandomly select a row that satisfies this condition. Can I use the partial index that already exists to speed this up, and if so, how?


texts: AWS RDS MyISAM tables from mysql and information_schema and good snapshots

texts:
I'm wondering about the topic of moving all MyISAM tables to InnoDB in RDS. Mostly because I have this annoying warning every day in the RDS console. One thing is pretty clear, the tables of the customer applications must be altered to InnoDB (if possible). No question about that. But what worries my is the "system" tables. If I run this query:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM'

I get lot of tables with MyISAm tables that are not from the customer database but the MySQL itself, for example:

information_schema | COLUMNS                   | MyISAM |
information_schema | EVENTS                    | MyISAM |

Let me call it "system tables".

It is kind of mandatory to also ALTER this "system tables" to InnoDB? I did not found any specific of this tables, and the warning message of RDS is not clear about that:

  DB Instance db-byhours-56 contains MyISAM tables that have not been
  migrated to InnoDB. These tables can impact your ability to perform
  point-in-time restores. Consider converting these tables to InnoDB.
  Please refer to
  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html#Overview.BackupDeviceRestrictions

Any ideas or comments?

Thanks !

question bonus: what about other kind of engine like MEMORY? I have some customer with few tables like that engine. Can be this engine also a problem to get good snapshots?


texts: MySQL Partitioning: why it's not taking appropriate partition

texts:
DROP TABLE temp;
          CREATE TABLE `temp` (
         `CallID` bigint(8) unsigned NOT NULL,
         `InfoID` bigint(8) unsigned NOT NULL,
         `CallStartTime` datetime NOT NULL,
         `PartitionID` int(4) unsigned NOT NULL,
         KEY `CallStartTime`(`CallStartTime`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
         PARTITION BY HASH (PartitionID)
          PARTITIONS 366

I use EXPLAIN in a sample query I get the next result:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 1

or

 EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = DAYOFYEAR('2013-01-01 10:24:00')

result:

   id   select_type table   partitions  type    possible_keys   key key_len ref rows    Extra
  1         SIMPLE          temp    p1  ALL                 2   Using where

I don't know why it's using the p1 partition. Here parititon start with p0


texts: Optimize query to not read all rows in table

texts:
I have this in my slow query log.
Slow Query Log
# Time: 2020-03-09T08:42:46.946084Z
# User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.001558  Lock_time: 0.000278 Rows_sent: 0  Rows_examined: 255
SET timestamp=1583743366;
select DISTINCT q.id,q.*
            from bot_message_queue q
            left join bot_message_queue_wait w1 on q.botId=w1.botId AND q.chatId=w1.chatId
            left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0
            where
              q.status=0 AND

              (w2.retry_after IS NULL OR w2.retry_after < 1583743366) AND
              (w1.retry_after IS NULL OR w1.retry_after < 1583743366)
            order by q.priority DESC,q.id ASC
            limit 1;

My tables
Following the DDL for the tables involved in the query.
bot_message_queue
CREATE TABLE IF NOT EXISTS `bot_message_queue` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `message` longtext,
  `add_date` int(10) UNSIGNED NOT NULL,
  `status` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=waiting,1=sendig,2=sent,3=error',
  `priority` tinyint(1) UNSIGNED NOT NULL DEFAULT '5' COMMENT '5=normal messages,<5 = bulk messages',
  `delay_after` int(10) UNSIGNED NOT NULL DEFAULT '1000',
  `send_date` int(10) UNSIGNED DEFAULT NULL,
  `identifier` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `botId` (`botId`,`status`),
  KEY `chatId` (`chatId`,`status`),
  KEY `botId_2` (`botId`,`chatId`,`status`,`priority`,`identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

bot_message_queue_wait

CREATE TABLE IF NOT EXISTS `bot_message_queue_wait` (
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `retry_after` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`botId`,`chatId`),
  KEY `retry_after` (`retry_after`),
  KEY `botId` (`botId`,`chatId`,`retry_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bot_message_queue_wait`
--
ALTER TABLE `bot_message_queue_wait`
  ADD CONSTRAINT `message_queue_wait_botId` FOREIGN KEY (`botId`) REFERENCES `bot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Question
It's not slow, but it is reading the whole table on every request.
What indexes should I change/add to fix this?
UPDATE
Explain result without distinct

UPDATE 2
This is a very heavy read/write/update table, its the single most used table in my entire database, usually it has just less than 1000 rows but it could go up to 10000+ when someone is sending bulk messages ...
I clean up processed rows every hour to keep it small ...


texts:
I can't find decent strategy for searching titles.

I have table for movies.
Each movie has 5-10 alternative names that are stored in movie_names (id, name, name_clean, movie_id) table.

To normalize all names I use script that converts all names in ascii, removes whitespace, punctuation, etc. and place it in name_clean.

And on search I use

SELECT DISTINCT(movie_id) FROM movie_names WHERE `name_clean` LIKE '%f%' ORDER BY views DESC

And then I use ids and select with "where in" movies table.

Although this strategy works, but because of my name cleaning the search is very wide. I want to improve it without name cleaning, but wonder if I can improve it better.

For example I was thinking about search LIKE 'f%', but then I will need to explode every movie name in words.
And if I have movie name "My very fat big movie name" I need to make this much of additional rows:

"My very fat big movie name"
"very fat big movie name"
"fat big movie name"
"big movie name"
"movie name"
"name"

Also on people table I use query

 SELECT * FROM people WHERE `name1` LIKE '%f%' OR name2 LIKE '%f%' ORDER BY views DESC

While searching movies the speed is okay, but it is very wide and selects too much results (search column is latin_swedish), with people table it's very slow (columns are utf8) and there are more rows.

How would you organize search in mysql.


texts: Find Without primary key Table in SQL Server 2008 R2?

texts:
This is my query to find out primary key, foreign key in SQL Server 2008 R2:

select  *
from information_schema.table_constraints
where constraint_type = 'PRIMARY KEY'

Know I need to select without primary key table in my database.

Thanks


texts: DB relationships with historical tables review

texts: Idea is simple. User -> Projects -> Sprints -> Tasks, but I need to keep historical versions for them. It will fit to my requirements or there are some changes to be applied? - or different approach.


texts: Why is my transaction log file growing so large after disabling replication?

texts:
In Why Does the Transaction Log Keep Growing or Run Out of Space?, two common reasons are outlined for why a transaction log grows:

The database is in full recovery mode, and log backups are not being
taken.
A long running transaction is taking up a lot of space.

As far as I can tell, neither of these reasons apply to me. My database is in simple recovery mode, and there are no long running transactions taking up a lot of space.

Note that I got lucky when I took the screenshot for DBCC OPENTRAN and there were no transactions active. Most of the time I see and see a transaction that started seconds ago.

Another answer suggests that my log might be large, but empty. To check this, I right clicked on the database, and navigated to Tasks -> Shrink -> Files, and selected the Log file type. I can see that my log file is large and also has very little free space.

I used Aaron Bertrand's query to figure out which sessions are causing the log file to grow and on what interval.  The query tells me that the log file grows on a semi-regular interval, about once every 10 minutes. The SPID is different every time, and doesn't always come from the same application. I used a query (that I can't find now for some reason) to figure out what SQL statement that session was last executing. I studied those queries to see if anything stood out and didn't see anything obvious. I ultimately decided that if DBCC OPENTRAN said no transactions were running long, then the problem must not be in the queries being executed by the applications.

We recently attempted to set up transactional replication with this database as the source. We ended up having to abandon, but had issues removing the publication. We had to manually drop the distribution database and use sp_removedbreplication to clean up. I read that replication can cause the transaction log to grow unchecked. My thoughts are that since deleting the publication didn't go so smoothly, that maybe the log file is growing because some remnant of the replication stuck around.

So, aside from the two most common reasons, what else can I do to figure out why the transaction log is growing so large (and fix it)?


texts: SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning

texts:
I want to validate the optimal performance of our SQL Server and in doing so, I have gone through all the performance counters listed at the following link and create a trace log to analyze the data.

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

I have gone through three days worth of collected data and compared it to the recommended values listed in that chart, and though most of my values look good, there a just a few that are outside of the recommended values listed and I want to get some feedback from others on how their systems compare and if any of my values should raise a red flag.

As a point of clarification, our SQL Server is 2008, running on a virtual machine, with 4 cores and 8 GB of memory.  The SQL VM connects directly, via iSCSI, to our SAN which has three separate disk groups (separate spindles) for the Database files, Log files and TempDB, and all are a RAID 1 configuration.

Here are the counters that I am concerned by:

For the SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms.  According to the chart listed above, it states that this value should be around 0 ms.  Why would my value be significantly higher?
For SQLServer:Databases\Log Growths, I'm at 325 and for SQLServer:Databases\Log Truncations, I'm at 14200.  I assume that this is since the start of SQL Server, correct?  If so, the chart says that growths should be around 0.  Is this something I should worry about?  The last reboot of the server was a month ago and there are about 100 databases on the server.
For the Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).  According to the chart, this value should be less than 10.  Should I be concerned by these spikes?
Finally for the PhysicalDisk\Avg. Disk sec/Write counters:

4a. For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.  The guide states that these values should be less than 20 ms. Should I be concerned by these tenth of a second spikes?
4b. For the Log Files volume, it stays almost steady at 8 ms.  Is it normal for the Log Files to have consistent writes with no break?
4c. Regarding these disk counters, in Windows Disk Management > Properties > Policies, the disks don’t have the “Enable write caching on the device” checked nor the “Turn off Windows write-cache buffer flushing on the device” checked.  Should these be enabled for a SAN that has a battery backed cache?  Could this be the cause of 4a and 4b?  If so, can they enabled during production hours without interrupting data flow?

Any input in regards to what others see on their production systems or if any of these values seem abnormal are appreciated.


texts: Mysqlcluster data nodes synchronization rate

texts:
mysql cluster version: MySQL-Cluster-gpl-7.4.12-1.el6.x86_64
num of datanodes: 2
num of sqlnodes: 2
num of mgmt nodes: 2

Recently one on my datanode server was down(node4) due to power outage. when i brought the disconnected datanode up i realized that the synchronization is very slow when it reached 17%.

[root@mgm1 ~]# ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 3: Data usage is 18%(493874 32K pages of total 2621440)
Node 3: Index usage is 6%(71005 8K pages of total 1048608)
Node 4: Data usage is 17%(458693 32K pages of total 2621440)
Node 4: Index usage is 6%(70800 8K pages of total 1048608)

i checked the latest row on specific table and saw that the result is same which means result is same when accessing through both SQL nodes when table is updated.

Would you kindly enlighten me

Q1: How to know what is causing this slowness. I guess it is due to huge number of table indexes but not sure that index build up is taking this much time.

Q2: will it have negative impact if i do DML(insert/update) on SQL nodes when sync is on progress ?
note: (freeradius is connected to the sql nodes which constantly inserts data)


texts: SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end

texts:
For reasons, I am using the following here-doc approach to export data to CSV.
$ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh > arca.csv <<EOF
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET NEWPAGE NONE
SELECT
    *
FROM ORDER.SALES
WHERE ROWNUM < 100;
EOF

This however produces a CSV file with empty first line and with unquoted carriage return at the end, as complained by psql tool when I want to load to Postgres
Below error happens because the CSV file has blank row as first line. The column names are read as data instead, causing type error.
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR:  invalid input syntax for type date: "FILE_DATE"
CONTEXT:  COPY arca, line 2, column file_date: "FILE_DATE"

If I remove the blank row manually, the following error then occurs because of carriage return character at the end of data
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR:  unquoted carriage return found in data
HINT:  Use quoted CSV field to represent carriage return.
CONTEXT:  COPY arca, line 101

How do I correctly use sqlcl with here-doc approach to produce CSV file without first blank line, and carriage return ending?


texts: Removing chat history for a particular user from database

texts:
I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far

I've added an option where the user can Clear Chat history for a particular conversation , but I'm having trouble with the concept of clearing history for a particular user in wish case if user-1 hit clear history all the messages of this conversation will not be displayed for user 1 but will still be seen for user-2

How can I upgrade my database schema so I can make this functionality ?

PS : yesterday i asked the same question on stackoverflow website and someone gave me a solution .but the solution he gave me is complicated and it didn't fill my requirement .
my database is currently supporting only private chat between 2 users


texts: Does 2 in the DBCC LOGINFO column "Status" mean it cannot be truncated?

texts:
When I run the command DBCC LOGINFO, a few of the Status rows contain 2.

Now I want to check why my transaction log cannot be truncated. How do I see this? Does the 2 mean in the Status that they are not reusable or an action is waiting (backup, restore)? Is it saying that it cannot be truncated?


texts: inconsistency between @@global.gtid_executed and mysql.gtid_executed


texts: Differential backup job is working but not the Full daily backup

texts:
I have a full backup job for my database that runs once daily, I see it posted daily in the folder that it should go to, but when I check the "View History" for this job in SQL Server Agent, there are red X's which tells me that it didn't run successfuly...

The opposite is happening for the Differential job that I see running successfully in the job history and posted successfully too in the destination folder.

I'm planning to restore both of these copies to see if they contain current updated data, but regardless, if I see the full .bak file in my folder, shouldn't the job show that it ran successfuly with a green checkmark?


texts: How does like query work with indexed table

texts:
I am working in Spring-java and mysql.

I have to query the table of size 100k records. Table has say 10 columns.
And In my sql select query I have to make like queries with %text% search on say 4 columns. Those 4 columns are varchar(200), having average text size of 30 character.

I have gone through few blogs and SO answers to understand about index and after reading I came to this question.

Will making those 4 columns individually FULLTEXT index, makes difference in execution time of like query?

Thank you


texts:
I am always puzzled regarding some mysterious t-sql behavior, like the following

-- Create table t and insert values.
use tempdb
CREATE TABLE dbo.t (a INT NULL);
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);
GO
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
go
-- expect 3 rows returned but only 2 returned (without null value row)
select * from dbo.t where a = a

This is not about how to retrieve all rows in a table and also not about avoiding use of ANSI_NULLS.

I just want to solicit some insights why t-sql behaves like this.


texts: What are reasonable privileges to grant typical users?

texts:
I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation:

root
developer
application

root is self explanatory. For developer this user needs to be able to easily access any database, make adjustments to it, etc. For starters I'm setting this user to this privilege set:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

application has an even more limited set. It should just be limited to manipulating a specific database.

I'm not sure what a reasonable set of privileges is to grant. What are a reasonable set of privileges to grant a developer and an application and why?


texts: What is the StatementParameterizationType plan attribute?

texts:
I have noticed execution plans sometimes include a StatementParameterizationType attribute.

What is this, what does it mean, and when does it appear?


texts: How to make mongos to be in a replica set?

texts: I have a shard cluster: one mongos, three shard replica sets and one config replica set. My application only talks to the mongos for read/write to mongodb. I wonder whether I can make the mongos replica. What I mean by this is that in case of a mongos is down, I need a slave of the mongos to become the primary. There is --replSet parameter on mongod but mongos doesn't have such parameter. How can I make it in mongos?


texts: SQL query max aggregate selecting correct nonaggregate columns?

texts:
I am confused about the max() aggregator, I think this query makes sense...
select title, vote_avg, floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where (vote_avg, decade) in (
   ...>   select max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...>   from films
   ...>   where vote_cnt > 100
   ...>   group by decade
   ...> )
   ...> and vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            vote_avg  decade
-------------------------------  --------  ------
Homecoming: A Film by Beyoncé    8.6       2010
Spirited Away                    8.5       2000
Dilwale Dulhania Le Jayenge      8.9       1990
The Empire Strikes Back          8.4       1980
We All Loved Each Other So Much  8.6       1970
Psycho                           8.4       1960
12 Angry Men                     8.4       1950
The Great Dictator               8.4       1940
City Lights                      8.4       1930
Sherlock Jr.                     8.2       1920
The Immigrant                    7.5       1910
The Great Train Robbery          7.2       1900

but I am not sure why this one works?
select title, max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            max(vote_avg)  decade
-------------------------------  -------------  ------
Homecoming: A Film by Beyoncé    8.6            2010
Spirited Away                    8.5            2000
Dilwale Dulhania Le Jayenge      8.9            1990
The Empire Strikes Back          8.4            1980
We All Loved Each Other So Much  8.6            1970
Psycho                           8.4            1960
12 Angry Men                     8.4            1950
The Great Dictator               8.4            1940
City Lights                      8.4            1930
Sherlock Jr.                     8.2            1920
The Immigrant                    7.5            1910
The Great Train Robbery          7.2            1900

I'm using sqlite3, which of these queries is better practice? (Or are they both incorrect?)


texts: Trying to figure out when a table was dropped?

texts:
Possible Duplicate:
  Determining how a schema change occurred?

Using SQLServer 2008, I see in my schema a particular table was dropped.  I am trying to figure out when / why etc?  Is there any commands I can run to see when the table was dropped, or anything that could help me out.

Thanks.


texts:
I have an web application that has a 1TB MySQL 5.7 production database.
We are running into performance issues due to table data size. We are planning to archive old data from different tables. Checking for best options.
Requirement
Users do CRUD operations every day with minimal data so we plan to remove old data (1 year back data) to separate archive DB server and remove the old data from the production DB, which will make the users CRUD operations fast.
Challenge
Production DB to archive separate DB sync will happen every day. Production DB to separate Archive DB server sync only Create, Updated data should sync. Where users use separate archive DB server when required to see all data.


texts: SQL Server: Log Shipping in "restoring" state: Can I temporarily take it out of restoring to check it?

texts:
I know it is normal for the target db to be in "restoring" state

I also would like to do a few queries as a sanity check that the data is all up to date.

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

thanks!


texts: Why entire table is locked instead of a single row?

texts:
Laravel Version: 7.16
PHP Version: 7.4.6
Database Driver & Version: MariaDB 10.4.13 InnoDb engine
Below laravel(php) code is going to return array response
Once action method called for id=1 it takes 20 sec to get committed.
Meanwhile the same method called for id=2 response waits until the first transaction is getting finished.
why row-level locking is not working.

The request cycle for id=1 and id=2 are independent and called within 20 sec.

protected function actions($id)
    {
      DB::beginTransaction();
      try {
          if($id==1)
            sleep(20);

          $invoice = Invoice::lockForUpdate()->find($id);
          $name = 'Approve';
          if ($invoice->approved) {
             $name = 'Disapprove';
            }
         } catch (Exception $e) {
            DB::rollBack();
         }
      DB::commit();

        return [
            'name' => $name,
            'id' => 'menu-approve',
            'data-status' => $invoice->approved
        ];
    }

A lockForUpdate() locks a specific row until it was committed successfully (Select * from invoices where id=1 FOR UPDATE).
But select * from invoices where id=2 FOR UPDATE should not be wait until the first query get committed.
Also without specifying a lock ( Select * from invoices) records are not drawn and it waits the first transaction to be committed.
What could be the reason that entire table is locked instead of a single row?


texts:
I want to assign values to null cells of a column as

UPDATE table 1
SET number = MAX(number) + 1
WHERE number IS NULL

but mysql return error Invalid use of group function.


texts: Strange upper() behaviour for dateranges in PostgreSQL

texts:
I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query:

select  upper(('[2005-12-01,2005-12-04]')::daterange)

It returns 2005-12-05 instead of 2005-12-04.

The following query excludes the upper bound by specifying ) instead of ]:

select  upper(('[2005-12-01,2005-12-04)')::daterange)

It returns 2005-12-04.

Isn't the upper() function supposed to return the upper bound limit of a given range? If so, by specifying [2005-12-01,2005-12-04] as the range, it should return the upper bound which is 2005-12-04. It returns 2005-12-05 instead, which is very odd to me.

Any explanation on why this happens? I only know that '[2005-12-01,2005-12-04]'::daterange is translated by PostgreSQL to '[2005-12-01,2005-12-05)'::daterange, but this doesn't explain why 2005-12-05 should be the upper bound when I have explicitly specified 2005-12-04 as the upper bound.


texts: Why does sp_columns / sys.spt_columns_odbc_view return incorrect results?

texts:
While investigating a question on Stackoverflow, I discovered that sp_columns, which uses sys.spt_columns_odbc_view, is returning the wrong / unexpected DATA_TYPE & TYPE_NAME for NVARCHAR(MAX). It's returning NTEXT for NVARCHAR columns and I can't figure out why. Does anyone have any insight?

DEMO

CREATE TABLE [dbo].[deleteTable](
    [v] [nvarchar](max) NULL,
    [t] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

select
    c.name
    ,c.system_type_id
    ,ty.name
    ,ty.precision
    ,t.name
from
    sys.columns c
inner join
    sys.tables t on t.object_id = c.object_id
inner join
    sys.types ty on ty.system_type_id = c.system_type_id
where
t.name = 'deleteTable'

exec sp_columns @table_name = 'deleteTable'


texts:
i want to select first and last score from such data

WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
),
first_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at ASC
),
last_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at DESC
)

select
    md5(u.user_id::varchar) user_id,
    u.created_at signup_date,
    fhs.created_at first_score_created_at,
    fhs.score first_score,
    lhs.created_at last_score_created_at,
    lhs.score last_score
from t as u
    INNER JOIN first_score fhs ON fhs.user_id = u.user_id
    INNER JOIN last_score lhs ON lhs.user_id = u.user_id;

my question: is exists a way to make one subquery to join the first_score and last_score in single query?


texts: is it "bad" to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?

texts:
pertaining to a previous question I asked:  how do you prevent dead rows from hanging around in postgresql?

If I have a table with 300k rows, and I were to update all rows in the table, setting a boolean column from false to true, is this an OK thing to do or should bulk updates over a certain size always be avoided?  I'm still a bit fuzzy on the whole Vacuum thing here, my understanding is that the MVCC in postgres causes every row to be duplicated temporarily and then auto-vacuum should come in an reclaim that space.  I am wondering, hypothetically, if these 300k rows were to have that boolean value flipped once a week, would I eventually run out of disk space, or would this not be a problem as long as there was enough time between the updates for auto-vacuum to come and keep things in check?


texts: Find out what setting were used for a given stored procedure

texts:
As a follow-up to my previous question:

is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ?

It seems my customer there has several stored procedures and a few triggers that have been "tweaked" - but unfortunately, using a "wrong" set of those database settings. Can I find out which procs and triggers are affected, or do I need to re-create all of them with the proper settings?


texts: Is it possible to create AWS RDS as replication from external server?

texts:
I can't find any tutorial to create a new instance of AWS RDS (MySQL) to be an replication db from an external db server. Is it possible to create one?

Thanks.


texts: How can I check if my user has CREATE SCHEMA permission?

texts:
I have a user account in a database that can create tables and stored procedures.
I would like to confirm if this user account can create schemas - without trying to do it first.  (I am part of finance not IT.)
If I can create schemas, then I will ask IT for permission to go ahead and do so.
If I can't then I will just continue to wait for IT to do it on the Service Desk request I put in some time ago (sigh).
Is there a command I can run in SSMS that will tell me this without actually creating a new schema?
I have also taken a couple of screenshots of this user's permissions - maybe they will have the answer?

EDIT:
Having run the SQL suggested by SQLServing below, I get this result:
UserName    is_disabled principal_type_desc class_desc  object_name permission_name permission_state_desc
DWReports   0   SQL_USER    DATABASE    NULL    CONNECT GRANT
DWReports   0   SQL_USER    OBJECT_OR_COLUMN    GetSystemSQLAgentJobList    EXECUTE GRANT


texts: High CPU usage on SQL server - Slow queries

texts:
Our MS SQL Server is using about 95% of the CPU-power.

After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.

When it's over 80%, every query is extremely slow.

Our website is dealing with alot of big queries, so some of them takes 45-60 seconds.
After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.

How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?

There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.

Most of them are already pretty well optimized.

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

SQL: Microsoft SQL Server 11.0.2100.60
OS: Windows Server 2012 x64
Processor: 2.30 GHz
RAM: 4.00 GB


texts: How to preform a query as argument in PL/pgSQL function which returns JSON?

texts:
I'm trying to perform select by using already created function which returns JSON which aggregates into JSON array, but I'm getting problem in line where I'm performing a SELECT-clause.
Tables I have problem querying:
create table orders (
    order_id bigserial not null, -- this is Primary Key
    total double precision,
    order_date timestamp,
    user_id bigint -- references `users` table
);

create table order_item
(
    order_item_id bigserial not null, --primary key
    amount integer,
    book_id bigint, -- FK which references `book` table
    order_id bigint -- FK which references `orders` table
);

My query looks like this in PL/pgSQL:
create or replace function public.get_order_by_order_id(o_id bigint) returns json as
$BODY$
DECLARE
    order_items json;
    found_order "vertx-jooq-cr".public.orders;
    found_user json;
begin
    -- other queries left out for code brevity

    select json_agg(x) INTO order_items
    from (select public.get_orderitem_by_oi_id(
        select oi.order_item_id -- this is where PROBLEM occurs !!!
        from public.order_item AS oi
        where oi.order_id = o_id)
    ) x;

    return (select json_build_object(
        'order_id', found_order.order_id,
        'total_price', found_order.total,
        'order_date', found_order.order_date,
        'user', found_user,
        'order_items', order_items
    ));
end
$BODY$
language 'plpgsql';

BTW, function get_orderitem_by_oi_id(order_item_id::bigint) works properly and returns JSON.
In line where "-- this is where PROBLEM occurs !!!" comment is placed I get an error/warning in DataGrip  which states:

')',  or ORDER expected, got 'select'

This is how my JSON should look like:
{
  "order_id": 21,
  "total_price": 89.92,
  "order_date": "2020-05-03 00:00:00",
  "order_items": [
    {
      "order_item_id": 32,
      "amount": 3,
      "book": {
        "book_id": 2,
        "title": "Murder on the Orient Express",
        "price": 19.98,
        "amount": 151,
        "deleted": false,
        "authors": [
          {
            "author_id": 1,
            "first_name": "Agatha",
            "last_name": "Christie"
          }
        ],
        "categories": [
          {
            "category_id": 9,
            "name": "Crime",
            "deleted": false
          }
        ]
      },
      "order_id": 21,
      "total_order_item_price": 59.94
    },
    {
      "order_item_id": 31,
      "amount": 2,
      "book": {
        "book_id": 5,
        "title": "Harry Potter and the Prisoner of Azkaban",
        "price": 14.99,
        "amount": 85,
        "deleted": false,
        "authors": [
          {
            "author_id": 4,
            "first_name": "JK",
            "last_name": "Rowling"
          }
        ],
        "categories": [
          {
            "category_id": 3,
            "name": "Tragedy",
            "deleted": false
          }
        ]
      },
      "order_id": 21,
      "total_order_item_price": 29.98
    }
  ],
  "user": {
    "user_id": 1,
    "username": "test"
  }
}

My question is is it possible to query for adequate order_item_id (from order_item table) using get_orderitem_by_oi_id(order_item_id::bigint) function  by passing adequate order_id in query and if not this way is there ANY other suitable way to achieve this?
Any kind of help in solving this problem is greatly appreciated.


texts: How to delete all rows from all tables in postgresql?

texts: Is there any SQL query to delete all rows from all tables in postgresql?


texts: Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)

texts:
I've been trying to search for this info on oracle documents but they just don't talk about it.

I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (encrypted one). Do I need to have temp space to do it (2TB)?


texts:
I'm doing merge replication between two SQL Server 2008 machine. But, when I attempt to do any modification on subscriber system, I get the following error.

  The merge process could not enumerate changes at the 'Subscriber'.
  When troubleshooting, restart the synchronization with verbose
  history logging and specify an output file to which to write.
  (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)

Regards,


texts:
I am new to the SQL Server world. In my example below, I have two companies:

Building Blocks
Red Cement

Only one entry for each company has a value for the columns power1, power2, power3.

I need to update the table so that each company has a value for all columns. In other words, I need to update all blank (null) values.

Example with made-up data:

Create Table #Rentarious
(
  c1 varchar(200)
  ,yryryryr datetime
  ,power1 varchar(200)
  ,power2 varchar(200)
  ,power3 varchar(200)
)

Insert Into #Rentarious VALUES
('Building Blocks','2016','Red','Blue','Green')
,('Red Cement', '2012', 'Pink','Purple','Orange')

Insert Into #Rentarious(c1, yryryryr) VALUES
('Building Blocks', '2012')
,('Building Blocks', '2013')
,('Building Blocks', '2014')
,('Red Cement', '2016')
,('Red Cement', '2011')

Question

What would the syntax be to write an update statement to update the null values for columns power1, power2, power3 to the values already listed in the table?


texts: How to select the first row of each group?

texts:
I have a table like this:

 ID |  Val   |  Kind
----------------------
 1  |  1337  |   2
 2  |  1337  |   1
 3  |   3    |   4
 4  |   3    |   4

I want to make a SELECT that will return just the first row for each Val, ordering by Kind.

Sample output:

 ID |  Val   |  Kind
----------------------
 2  |  1337  |   1
 3  |   3    |   4

How can I build this query?


texts: How do you move a database from one server to the other using SQL Server Management Studio?

texts:
I need to move a database, but I want to make sure I do it safely and with all triggers, stored procedures and functions.

I was think to simply detach the database from my server using SQL Server Management Studio and move the .mdf and .ldf files to the new directory and attach it to the new server.

Can someone guide me please?


texts: Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

texts:
We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment.  However, none of us are formal DBA's so a lot of what we are doing is guessing and reading articles while attempting to make an informed decision.

Firstly, we are currently a small company who takes in several hundred thousand records (about 1/2 of them have image data) in a month.  We are growing quickly, and after a severe failure (a SAN array went down completely and we lost everything over a 12 hour period), we are moving to a new hosting facility with a better disaster recovery.

The new hosting facility will have our database on a 50 disk SAN.  Every minute, a snapshot is taken.  Should the SAN fail, the snapshot is loaded to another SAN, and the server is automatically brought up on the new SAN.  Downtime is a few minutes.

In addition to this, we want a redundant database setup.  We discussed having a Sql Server cluster using DFS on 2 separate SANs, but that violates PCI Compliance, which we must have.  Therefore, it is my opinion that a Cluster buys us no more redundancy than the hosting company's built in snapshot system in the event of a SAN failure.

The other option we are discussing is using mirroring.  However, the information we have read leads us to believe that mirroring with a witness will affect performance too greatly.  One option presented was to use "Safety Off" mirroring until we want to perform maintenance, then fire up the witness, take one server down at a time and perform the maintenance. Then, when complete, power down the witness.  This seems the best of both worlds, but also a headache.

So now the big question - With the snapshot system of our hosting company taking our downtime to mere minutes, what configuration is recommended to give use high availability as well as the best performance?


texts: MySQL To Show Month Over Month Data By Year

texts:
How can I change this query to show data in this format

01-18  2000
01-19  4000
02-18  3000
02-19  1000

This is the syntax I have but since it's union I can't use an order by clause

SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName,    SUM(saleAmount)
FROM  prevyear
WHERE BDM = 'Jason Hernandez'
GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
UNION ALL
SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName, SUM(saleAmount)
FROM  curryear
WHERE BDM = 'Jason Hernandez'
GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName


texts:
We are creating scripts to sent to our customers, with some inserts. We save them with ANSI encoding on NOTEPAD++, but when the customer runs the script via SQL*PLUS, it looks like this:

How can I be sure that the customer will run the script, with ansi encode, so we have no problems with those symbols on â, ó and et.

We told them to customize their environment to use NSL_LANG = WE8MSWIN1252.
TEST QUERY:

Select *,
Case
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
END as STATUS_TESTE,
Case
       When Teste_CHAR=Teste_CHAR_ASCII
       Then 'Everything is OK'
       Else 'Script will not run. your encode is different from ours'
END as Mensagem_TESTE
 from (
Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII
) A

as said here ( **How to be sure that a SQL Script is running with ANSI encoding? ) **this is not correct. But, is there a way to use something like this?


texts: Choosing the right algorithm in HashBytes function

texts:
We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario?

We want to ensure the risk of having duplicate hash value for two different nvarchar value is the minimum. Based on my research on the internet MD5 seems the best one. Is that right? MSDN tells us (link below) about the available algorithms, but no description on which one for what conditions?

HASHBYTES (Transact-SQL)

We need to join two tables on two nvarchar(max) columns. As you can imagine the query takes along time to execute. We thought it would be better to keep the hash value of each nvarchar(max) data and do the join on the hash values rather than the nvarchar(max) values which are blobs. The question is which hash algorithm provides the uniqueness, so that we don't run into the risk of having one hash value for more than one nvarchar(max).


texts: MySQL Workbench - specify Server/User/Pass on command line?

texts:
I am currently testing Privileged Access Management solutions. I have a need to open the MySQL Workbench with a command line that specifies the server name, and user/password of the privileged account that is being used for that session.

In MSFT SQL Server Studio, you can start it with '-S servername -U username -P password' There doesn't seem to be a straightforward way to do this using the MySQL Workbench.  There is a '-query' option, but I can't find anything that specifies how to fully start the app with those parameters supplied dynamically.

Can anyone suggest a way to do this with MySQL Workbench?


texts:
I'm new to SQL server and need your advice.

I have a database that shows 188GB as the size in the database properties window.

When I take a backup of the database, that backup file is only 65GB.  The database properties window still shows the database itself is 188GB.

Why is there a difference in the size of the backup compared to the actual database?  I'm not using backup compression.


texts: select based on sum of rows compared to another field value

texts:
I have a table of invoice items. A single transaction may result in multiple debit items and multiple credit items sharing the same invoice_set_id, I need to compare the sum of the debit items against the sum of the credit items and add the invoice_set_id to the result set if sum(debits) > sum(credits). It should also add to the result if there is no row with a credit amount. Using mySQL. Thanks for any help. Example table and result follows:

invoice_item_id  invoice_set_id  credit_debit  amount
62                a22             debit         15.00
63                a22             debit          8.00
64                a22             credit        23.00
65                b23             debit         44.00
66                c55             debit         15.00
67                c55             debit          2.00
67                c55             credit         8.00

Given the above, the result set should be:

invoice_set_id
b23
c55

Explanation: a22 is not returned because the debits and credits are equal, b23 is returned because it has a debit but no credit, and c55 is returned because the sum of the debits is greater than the single credit.

I appreciate any help with this. The actual query is more involved, but I think this particular problem is all I need help with.


texts: can mysql user be blocked on the local server but have remote acces?

texts:
So at work we have a windows server running mysql, the IT guy got fired but he supposedly left root password.

Try to login with root user:
  - Locally: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

- Remotely: "ERROR 1045 (28000): Access denied for user 'root'@'hp-01' (using password: YES)

Beacause i've been using a .net app which connects to that server I thought I'd give it a try with that user. it went like this:

- Locally: "ERROR 1045 (28000): Access denied for user 'adminpect'@'localhost' (using password: YES)

- Remotely: I stablished connection, but because it's not an admin user I only have limited acces to one database.

All this i tried from the command line and mysql workbench and in both I get the same results.

For the record I have very basic mysql knowledge and I've been searching all over the internet for this but have'nt had much luck.

Thanks in advance.


texts:
I have customer data in a table structure like this:

customerID | status       |
xxx        | 1            |
xxx        | 2            |
yyy        | 3            |
xxx        | 1            |
yyy        | 4            |
yyy        | 4            |

I would now like to see the different status each customer have

So the result would be:

customerID | status1     |  status2      |  status3      |  status4
xxx        |  2          | 1             |  0            | 0
yyy        |  0          | 0             |  1            | 2


texts:
On a QA server I need to give access to specific login  from  just one IP to log to this server.

I wrote This login  trigger

Create   TRIGGER tr_CheckIP ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @IPAddress NVARCHAR(50) ;
    If ORIGINAL_LOGIN()='test'

    --print ORIGINAL_LOGIN()
    begin

    SET @IPAddress = convert(NVARCHAR(50),ConnectionProperty('client_net_address'))
    --print @IPAddress
    IF NOT EXISTS ( SELECT  IP
                    FROM    master..IPAddress
                    WHERE   IP = @IPAddress )
        BEGIN

            ROLLBACK
        END
    End
END

However the trigger is preventing  the user from log although the IP exists in the IPAddress table.

Any idea where in the problem in the trigger


texts: security concerns over enabling data access for the current server

texts:
Included in the code of the creation of some stored procedures,
I have the enabling of data access as you can see below.

BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'enable the data access for the current server'
    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'
                        ,@optvalue =  'TRUE'
END TRY
BEGIN CATCH
        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH
GO

I find this handy when I know that I will be using the stored procedures through a call to OPENQUERY as shown on the example code below.

            declare @sql nvarchar(max)
            select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) +
 ',' + '''' +
'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

            INSERT INTO #Radhe
            exec ( @sql)

            SELECT * FROM #Radhe

This has been working fine for me, however, I am wondering of any possible security breach that I might open when I enable the data access.

Is it a good practice enabling data access this way?
Any reason why I should not do it?


texts: Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

texts:
Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS geometry type in the destination table:

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry(POINT)
);

INSERT INTO t
VALUES
    ( 1, ST_GeometryFromText('Point(0 0)')      ),
    ( 2, ST_GeometryFromText('Point(1 2)')      ),
    ( 3, ST_GeometryFromText('MultiPoint(2 3)') ),
    ( 4, ST_GeometryFromText('Point(5 23)')     ),
    ( 5, ST_GeometryFromText('Point(42 36)')    );

The error message tell's exactly what's wrong:

ERROR:  Geometry type (MultiPoint) does not match column type (Point)

But it lacks useful context information like:

How many rows of the INSERT have this problem?
What were the exact values?
What values were in the other columns of the offending rows? (In the example above: What were the IDs of the offending rows?)

Can I adapt the INSERT statement so that PostgreSQL would give me this information, e.g. include the complete content of the offending rows in the error message, like it does for violated NOT NULL constraints?

E.g.

CREATE TABLE s (
   i integer NOT NULL, t text
);

INSERT INTO s
VALUES
    (1   , 'foo'),
    (NULL, 'bar'),
    (2   , 'baz');

results in a much more useful message:

ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null, bar).

Motivation / Use-Case

When you can just look at the VALUES listed in the INSERT statement and see the offending rows, this is of course not that relevant. But the same issue arises when the inserted rows are selected from another table or computed dynamically, and then a more informative error message would indeed be useful.


texts: Copy a SQL Server database from one server to another on demand

texts:
I have two servers, Prod and Test, both running SQL Server 2008 RTM.

Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another?

The copy should of course include all contents of the database, with the exception of privileges so I don't lock myself out from editing the destination.

The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.

Additionally, the connection to the source should be read-only and (if possible) only able to initiate the copy process without actually having access to the data.

I am slightly familiar with PowerShell, so if this only means connecting and starting a task it should be doable. Or do I have to look for advanced solutions?

Thank you.


texts: MongoDB Server has authorization schema version 3, but found a schema version 1 user

texts:
Trying to restore a production MongoDB onto a dev MongoDB and getting this error

assertion: 17408 Server has authorization schema version 3,
but found a schema version 1 user: { _id: ObjectId('5256c667c8bf4cc093fa9794'),
user: "adminuser", userSource: "mongoUsers", roles: [ "userAdminAnyDatabase",
 "dbAdminAnyDatabase", "clusterAdmin", "readWriteAnyDatabase" ] }

Production Server is sharded and using version 2.6.3

Dev Server is a 2.6.3 and using version 2.6.3

Client being used to restore is version 2.6.1 (maybe this is the problem?)

Only the user databases are copied, the config, admin and users databases are not restored.


texts: Replacement for transactional replication

texts:
My colleagues and I have to migrate a database from SQL Server 2008 R2 to SQL Server 2012.

Schema, triggers, stored procedures etc. are working fine. But we also have to "migrate" the replication mechanism.

Replication is used to transfer data from the intranet to the dmz and vice versa. (There can be edits on both sides!!)

At the moment we use transactional replication with updatable subscribers.
The problem is: this mechanism isn't available in SQL Server 2012 any more.

Question: what is my best option to replace that feature?

Requirements:

Internal and external database can be edited
Near "real time". Maximum time until the edits show up in the other database sould be around 10 minutes.
Due to restrictions from the clients security team the connection attempts between databases may only be initiated from the internal database.

If I am correct Microsoft advises to use peer to peer replication as a replacement, but I am not sure that we can use that because of the security restriction.

Read/Write speed is not our first concern because there are only ~3000 users.
The application uses Entity Framework 4.5 (if that matters).

Any advice will be appreciated!


texts: Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

texts:
I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.

I have a web based form with multiple fields including a field that is hidden on the form that writes the CURRENT_TIMESTAMP to the database when the form is submitted.

The challenge I'm facing is that I want to create reports from these forms (sorting by month, week, or year) and order them by the timestamp but based on what I've seen here, sorting is not possible.

Is there an alternative I can use to achieve the results I want?


texts: Nosql multiple database design to handle sharing scenarios

texts:
This question is regarding design of a database in mongodb.

The scenario:

A user has a drive which he can share with others.
Drive has two categories: docs and others.
The user has three choices in sharing: full-drive/docs(ful or some)/other.
Plus he has the ability to share the docs(any change made thereon is reflected) / provide a snapshot of when it was shared(any new change is not reflected) with an expiry time.

Requirements:

I need to have different database to store drives and files and share database to handle sharing.(this is neessary as shared database will be acessed by other applications as well).

How I am doing:

When he is sharing a snapshot, I copy the data from his drive to the shared database doc.
But when he shares the docs permanently, I am sharing a link to the existing drive with info containing what is shared, so that I can retrieve accordingly. Plus if some of the docs are shared, then I have list of docs which I use to create view data.

Issues faced in my approach:

The drive should also know if it is shared with people or not. If I maintain like ids (with expiry timestamp) in the drive document, then it conflicts with the idea of having shared database, which is a necessity.
If I query the shared database for getting this flag, then its just adds more time, since most of the time, user just wants to see his docs and not even worry about the sharing of it.
If a user shares a doc/drive/otherInfo with some person, then it involves adding information in 3 or 4 collections which I am worried may fail in between. I am not sure about mongodb but I really wanted transaction like feature in here.

I want to know how I can design database for this sharing functionality in nosql scenario so this it efficiently addresses above mentioned scenario.
Thanks...
.


texts: I'm upgrading to 3.0 and ran into some issues with the upgrade. Specifically, I got an error when trying to start up mongod via ssh, it tried to use the default dbpath instead of the one I specified in my new YAML config file. I went ahead and rebooted the machine and now mongod is up and running again. I'm a bit paranoid at this point and would like to know if there's a way to make sure the storage engine is wiredtiger from the shell.


texts: Changing tx_isolation and binlog format on master server - do i also need to adjust slave?

texts:
We are running a Drupal Commerce site with MariaDB 10.0 and master/slave replication. Because of the way our application works, we end up running in to a ton of deadlocks under heavy load. Apparently this is a fairly common problem for large Drupal Commerce sites.

We've successfully tested changing two settings on our test servers (which are standalone; not master/slave) which has eliminated the deadlock problem:

transaction-isolation = READ-COMMITTED
binlog_format = ROW

My questions are:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

3) Do we need to 'stop slave' while applying this change?


texts: Converting char to varchar columns increases database size

texts:
I have a database (SQL Server 2005) with thousands of char columns. I want to convert these columns to varchar,
then I follow this steps:

Shrink Database
Check the current stats, general and detailed (each table).
Massive conversion from char(x) to varchar(x)
Massive trim to the updated columns
Shrink Database
Check the current stats, general and detailed (each table).

For point 2 and 6 I run this query:

DBCC UPDATEUSAGE(0)
EXEC sp_spaceused --to check the current stats
SELECT --to check detailed stats
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows

The problem is the size of the database after conversion (17062.50 MB) is considerably greater than the size before conversion (14975.13 MB).

If I make the comparison in a table with many varchar fields we can see more clearly:

         RowCounts     TotalSpaceKB     UsedSpaceKB     UnusedSpaceKB
---------------------------------------------------------------------
Before       77427            46440           45864               576
After        77427            85224           84664               560

The average column size for this table is:

Type         Average size (Sum(Len)/Count(*))
---------------------------------------------
varchar(12)                             11.05
varchar(100)                             1.97
varchar(12)                              3.29
varchar(70)                              3.84
varchar(150)                            32.54

What I'm doing wrong?

Thanks


texts: Efficiently remove 90% of a table's data

texts:
I am working on a process to remove 90% of a table's data because only 10% is needed for testing.

The best way I have found includes storing 10% of the table's rows into a temporary table.

Current method

SELECT TOP 10 PERCENT *
INTO #temp_some_table
FROM some_table (nolock)
ORDER BY some_column DESC

TRUNCATE TABLE some_table

INSERT INTO some_table
SELECT *
FROM #temp_some_table

DROP TABLE #temp_some_table

This method is filling up tempdb and causing the disk to fill up as well.

Questions

Is there a more efficient way to delete 90% of a table's data
ex (DELETE TOP 90 PERCENT FROM sometable)

or

Is there a way to insert 10% of some_table's data into a temporary table using batches? Something like this:

DECLARE @r INT;

WHILE @r > 0
BEGIN

BEGIN TRANSACTION;

INSERT INTO [dbo].[##temp_cds_Basket]
SELECT TOP 10 PERCENT *
FROM [dbo].[cds_basket] s

SET @r = @@ROWCOUNT;
print @r

COMMIT TRANSACTION

END

Possible solution

How about this?

SET NOCOUNT ON;

DECLARE @r INT;
DECLARE @TenPercentDate datetime
with cte (some_column) as (

    select top 10 percent some_column from some_table (nolock) order by some_column desc
)
select @TenPercentDate = min(some_column)
from cte

select @TenPercentDate

 SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

 DELETE TOP (10000) from
  some_table
  WHERE some_column < @TenPercentDate

  SET @r = @@ROWCOUNT;
  print @r

  COMMIT TRANSACTION;

  --CHECKPOINT;    -- if simple

END

--rollback


texts: MySQL auto Increment id has taken but seems like not written to disk

texts:
MySQL is having a very strange issue and we are unable to figure out. So I need expert advice to figure out the serious problem. I have tried to provide all possible details. But you can ask for more if you require more information.

We have one MySQL table(ticket) in a different database which got records inserted by triggers from some others databases tables(application databases). Before going further let me introduces database design and table structure.

We have more than 600 database tables with similar table schema with InnoDB engine.

Databases

db_20110624

db_20110706

db_20110825

and so on....600+

and logdb

We have after_insert and after update_trigger written to application database table. so as soon any record inserted/updated from the application side we got a track in logdb.tbl1 table. (where we are managing ticket base queue for further processing )

Information about logdb.tbl1

We are having around 3 to 4 lacks records in this table/day
current records in tables 12,273,398 ( 12 millions )
we are purging 1-month-old data from the tables regularly but since a month or 15 days we are not purging it.

Table structure:

'serialid', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment'

'requestunkid', 'bigint(20)', 'NO', 'MUL', NULL, ''

'fordate', 'date', 'YES', '', NULL, ''

'databasename', 'varchar(255)', 'YES', '', NULL, ''

Problem

We have one process on another server which selects data from logdb.tbl1  process and inserts those rows to another database server.

We are picking data in limit 100 from tickets.channelupdates and insert to another database server. to track the pending queue we are finding max(serialid) from another database server and select next 100 records from ticket.channelupdates. and sometimes it skipped some records to select.

Example:

| logdb.tbl1 |

serialid

78887794

78887795

78887796

78887797

78887798

78887799

78887800

78887801

78887802

.......

But when we run select, it gives a result with some missing rows.. (eg. it gives 78887794,78887795,78887796,78887800,78887801,78887802) here 78887797 to 78887800 records are missing. but when we run the same query with max number after some time.... it gives an accurate result. which is causing the very serious problem for us. some records got skipped due to this discrepancy.

Note the issue is random. which is happening very often since 15 to 20 days. moreover, we noted there are 1sec to 15 sec gap between the insert(to tbl1 table) and select(from tbl1 table).

Thanks for the help in advance.


texts: How to implement UNIX like ACL for table rows

texts:
EDIT: I can't create or use native MySQL users. I have to use users stored in table with their password hash.

I am new sql stuff so please forgive me for asking dumb question. I am creating my first real life application for college project.

At its core, It need to handle more than thousands of users which should not able to read or write to each others data unless given privileges. like Linux does with user and groups.

in below schema which I tried , a user can view(read) and edit(write) other users if they have read permissions.( r=2 w=1 r+w=3 ).

for example if cgroup_1 is admin and cgroup_2 is managers and unixperm is 32 then it means users in admin group can read+write(3) and users in managers group can only read(2)

create table cgroups
(
    id int unsigned primary key auto_increment,
    title varchar(100) not null unique,

    cunixperm  tinyint unsigned not null default 32 ,# r=2 w=1
    cgroup_1   int unsigned not null default 1 references cgroups (id) on delete cascade on update cascade,
    cgroup_2   int unsigned references cgroups (id) on delete cascade on update cascade
);

create table users
(
    id          int unsigned auto_increment primary key,
    username    varchar(255) not null unique,

    cunixperm  tinyint unsigned not null default 30, # r=2 w=1 3=r+w
    cgroup_1   int unsigned default 1 not null references cgroups (id) on delete cascade on update cascade ,
    cgroup_2   int unsigned references cgroups (id) on delete cascade on update cascade
);

create table many_users_in_many_cgroups
(
    user_id int unsigned references users(id),
    cgroup_id int unsigned references cgroups(id),
    primary key (user_id,cgroup_id)
);

insert into cgroups(title)
values ('admins'),('managers'),('writers');

insert into users(username, cunixperm, cgroup_1, cgroup_2)
values ('user1',30,1,null),
       ('user2',30,1,2),
       ('user3',22,2,2),
       ('user4',02,3,3);

insert into many_users_in_many_cgroups
values (1,1),(2,2),(3,3),(4,4);

Now suppose user 2 has logged into my app, How can I only show the user rows where he has read (2) or read+write(3) permissions.

if above schema is not (probably) appropriate pls give me an example with appropriate scheme

I am currently using MariaDB but open for solutions for others too.


texts: Can creating indexes colide with future database upgrades?

texts:
I have a mysql Database with very slow querys. I am using the slow query log to monitor those.

This is a database created by a third party software that we're using. Their developers didn't do any kind of optimization at this level on the database. Some indexes would certainly improve performance.

My main concern is that my indexes may collide with future database upgrades (structural or index creation).

By nature indexes are not invasive in the DB structure/data, but upon table change they may? prevent these from happening successfully.

Can I add indexes safely, with the certainty that they will be handled (maybe even dropped) if changes need to be done, or should I avoid that?


texts: How to guess or recover a timestamp conversion in sqllite?


texts:
I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network.  Each measurement is lightweight (between 10-20 bytes).  Now, I'm wondering what is the best strategy: InfluxDB + Zabbix? Or Telegraf? Or another alternative?

InfluxDB + Zabbix: according to

https://www.zabbix.com/integrations/influxdb
there is a possible integration. However, it seems the integration involves taking data that was collected in a Zabbix database and moving it to an InfluxDB to take advantage of InfuxDB using much less storage. So they meant it to be for archiving large amounts of data.
However, we believe Zabbix itself is too slow, no? The integration above would not work for online data collection, right? We need fast storage, and we do not want Zabbix to be a bottleneck. Are there known benchmarks and recommendations about Zabbix + InfluxDB integration, for online collection + storage?

InfluxDB + Telegraf: according to
https://www.influxdata.com/blog/monitoring-openwrt-with-telegraf/

there is also the possibility of integrating InfluxDB + Telegraf for data collection.  Are there best practices and/or benchmarks about data collection with Telegraf + storage with InfluxDB?

Are there other alternatives and/or suggestions for large scale data collection and integration with InfluxDB?


texts: Storing massive amounts of data from a sensor array

texts:
I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to 100,000 sensors. Each sensor sends a data sample every 10 seconds and each sample is 28 bytes in size.

Doing the sums thus leads to:

8640 samples per sensor per day
242kB of data per sensor per day
864 million samples per day

Now I have been wondering what the best way would be to store/retrieve the data ? I "joined" this project after the software has already been specified, so it needs to be implemented on a Windows Platform using SQL Server.

The current solution in my head is to create a DB with two tables to store the data samples. The first serves as a sort of an index into the second that stores the collated samples in a binary field on a per day per sensor basis :

Table 1:

  RecordID - BigInt - Identity
  SensorID - BigInt - Primary Key
  Date - DateTime - Primary Key (yyyy-mm-dd)

Table 2:

  RecordID - BigInt - Primary Key (from an insert into Table 1)
  Data - Binary

Basically I will write the samples from all sensors into temporary files (1 per sensor). At the end of each day I will then create an entry in Table 1, use the generated RecordID and dump the file into the Data field in Table 2.

This way I end up with only 100,000 entries into the table per day, instead of 864 million entries. The data should be available on the LAN or High Speed WAN, so retrieval of the sensor data on a whole day basis would be acceptable.

Although all data has to be stored, most of it will probably never be read. So the amount of reads on the table(s) will not be hugely more than the writes.

I know that I could implement something using the file system by just storing the path to the data files, but I read that SQL Server outperforms NTFS while your binary fields are less thank 256kB. (A gray area exists between 256kB and 1MB, while NTFS far outperforms SQL Server for binary sizes > 1 MB).

I am also slightly wary of storing data from 100,000 sensors into their own files without causes problems in the file system by either having huge amounts of files in a folder, or by having a complex tree structure with a few files in each folder, while not even taking file fragmentation into account.

Can anyone offer me some practical advice/comments on the above?
Are there obvious pitfalls that I am going to fall into?
The sample data does compress quite nicely. A 242 kB file compresses to about 85kB. Can I however implement some type of compression on database level so that the sample data (column) is compressed automatically?
Is SQL Server an obviously wrong choice for this project?
Is my design of the two tables wise, or could I just as well combine it into a single table that will still be as "performant" as the two tables?


texts: Is there a way to connect to postgres when have error "sorry, too many clients already "?

texts: I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error


texts: Trying to understand if annotations are necessary or not in ORMs

texts:
My previous thread was here https://softwareengineering.stackexchange.com/questions/328793/can-anyone-explain-this-one-to-one-one-to-many-many-to-one-many-to-many-conce but honestly I think it confused me more than it helped, especially when there seemed to be conflicting views even among the answers / commenters.

I'm not sure if my understanding is correct and I just lacked the vocabulary to express it, or if I have a serious hole somewhere that needs to be resolved with an explicit counter-example.

So I want to try this again with a more explicit example or two.

Example 1:

Let's say I defined two classes like so:

class A {
    private String name;
    private List<B> listB;
}

class B {
    private String name;
}

Now in this example I have not said anything explicitly (in terms of annotations) about whether the relationships are one to one, one to many in either direction, or many to many. I am going solely from the cardinalities of the containers.

To my understanding, this is a one to many relationship from A to B and the ORM would map this like so:

Example 2:

Let's say I defined two classes like so:

class A {
    private String name;
    private List<B> listB;
}

class B {
    private String name;
    private A referenceA;
}

I assume that this ends up with the same table mapping as Example 1.

Example 3:

Let's say I defined two classes like so:

class A {
    private String name;
    private List<B> listB;
}

class B {
    private String name;
    private List<A> listA;
}

I assume this is a many-to-many relationship, and an ORM would map it like this:

My question is whether or not my understanding is correct and whether or not this sort of approach results in any ambiguities (and what an example of that might look like).


texts: My problem is how do I maintain an index on table, weekly we are deleting and inserting bulk amount of data from the table.Because drop and create index taking too long time..