texts: What throughput should I expect with MPIO?

texts:
Dell PowerEdge 2950 with two 1 Gbps NIC's going to two 1 Gbps ports on a switch which then goes to a NetApp with four 1 Gbps NIC's that present as one virtual interface. 24 drives, 7200k SATA, NetApp RAID-DP. I've mapped each host NIC to the NetApp using MPIO in the Microsoft iSCSI initiator. Testing with SQLIO my write throughput appears reasonable at about 200 MBs, but my reads are closer to 100 MBs.

Shouldn't my reads be closer to 200 MBs just like my writes? Is this a configuration problem or is there a fundamental storage issue I don't understand?

Update:
Here are IOPS for the random workload. The reads make sense, though, I'm not sure what to make of 20000 for the writes. SAN cache is 3.2 GB. SQLIO tests are against a 25 GB file.


texts: PostgreSQL : cannot connect with a created role

texts:
I am using PostgreSQL 11.3 on Fedora 30. With the postgres user, I can use psql or connect to the cluster in PgAdmin 4, but when I create another user, for example :

CREATE ROLE myuser LOGIN PASSWORD '…';

I always get an error like :

FATAL: password authentication failed for user "myuser"

I had a look at other similar questions here (including this one, but I don't have a case folding problem), and I still cannot make it work.

Here is my pg_hba.conf file :

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

What I am doing wrong? Thank you for any help


texts: Ensuring relationship between child tables exists prior to SQL insert

texts:
I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.
I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.
The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I'm wondering if I can accomplish both in one query though. I'm not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.


texts: What is the significance of a semicolon at the end of SQL*Plus commands?

texts:
Some statements like create table, insert into etc take a semicolon at the end:

CREATE TABLE employees_demo
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
         CONSTRAINT emp_last_name_nn_demo NOT NULL
    ) ;

while others like

set echo on
connect system/manager

go through without the semicolon as well.

What is the reasoning behind this? How can I decide myself where to put the semicolon and where not?


texts: Statement based updates to a server using row based logging

texts:
I have two MySQL servers. Server A is doing statement based logging. Server B is doing row based logging. B is replicating from A.

Does B change the statement based SQL to row based SQL, execute the row based SQL and then log this row based SQL in its binary log, or does it execute the statement based SQL and then translates it to row based SQL before logging it?


texts: Dump sql cloud mysql instance for external replica

texts:
I followed this guide to create an external replica of a cloud sql server: https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica.I am getting this error when I try to start the newly configured slave:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

I suppose this is because the dump file created with
gcloud sql export sql...

does not include the CHANGE MASTER TO statement, and thus the replica does not know the executed GTID-s when the dump was taken.Has anyone faced this issue before? Is there a way to dump the sql cloud instance with --master-data?


texts:
I have data ActiveNonActive columns in  and the data are A,I,Empty  I have Derived Column to transform data  to bit i or empty--->0 and A->1 just want to ask how to use expression   transform the 'I' for 0
my expression

ISNULL(ActiveNonActive) ? (DT_BOOL)0 : (UPPER(ActiveNonActive) == "A" ? (DT_BOOL)1 : (DT_BOOL)0)


texts: How to migrate postgreSQL on EC2 instance to AWS RDS?

texts:
I have a large PostgreSQL DB (around 100GB) which is on EC2. I want to migrate it to RDS.

How can I achieve this with minimal downtime?


texts: Directly acessing the NULL bitmap of a row in SQL Server

texts:
I was reading the question Checking a wide table for nulls and I'm wondering if it's possible to somehow directly access the NULL bitmap of a row to fast check if a row contains NULL values. Would that be a reliable technique?

(From my reading, there might be issues with the newly introduced SPARSE columns since SQL Server 2008, but can't tell for sure.)


texts: AlwaysOn Availability Groups back up schedules

texts:
We have AlwaysOn Availability Groups primary and secondary replicas where the secondary replica is preferred for back up jobs to take place as long as it is available. I created back up jobs on both replicas and scheduled the jobs at the secondary replica.

My question is: - Do I have to schedule the jobs at the primary replica and then disable them until fail-over occurs and then manually enable jobs, or what is the best way to deal with these please?


texts:
I want to do column based replication which means:

In my server, I have table T1 in which columns are A, B, C, D, E

I want to replicate this table on different server in mysql 5.6 only, but with specific columns only, i.e   replicating only column A, C, E.

So

Is column based replication is possible in MySql ?
If I make view of T1 with desired columns and then replicate this view. Replicating view is possible ?? If yes, how ?
MySQL document says, if column differs in master and slave , then it should be in same sequence and same data types.


texts:
I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong?

mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen;

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join almacenes on almacenes.codigo = cajas.almacen' at line 1
  mysql>


texts: How to check with update trigger if only one column was updated postgresql

texts:
I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes with the flag not synchronized, but when this was processed, I update the same row with a flag synchronized , in this case I do not to write in the log table.
example:

Table: user( name, last_name, age, ..., sync_status )
IF (TG_OP = 'UPDATE' ) then

     if new.sync_status != syncstatus_pending then
        if old.sync_status = syncstatus_pending and new.sync_status != old.sync_status then
           raise notice 'Sync status updated, It means we are changing from sync pending to sync yes';
           return new ;
        else
           raise Exception 'The synchronization status is not configured correctly.' ;
        end if ;
    end if;
    -- Check if we are trying to update a not synchronized row
    if old.sync_status != syncstatus_yes then
       raise Exception 'It was not possible to update rows are not synchronized.';
    end if ;
    if new.name = old.name and new.last_name = old.last_name and new.age = old.age
    and old.sync_status= syncstatus_yes and new.sync_status= syncstatus_pending then
   -- all columns are the same except the sync one, it means that we require sync this row but not to add to log_table
    return new;
   else
   -- process to insert in the other 'log' table
   -- ...
    insert into log_table ( )
   end if ;
end if ;

is there any other way to check that only one column is being updated? ( indicating that is synchronized or not )


texts: Percona Xtrabackup on Suse EL 10 -- Failing

texts:
I have evaluated Percona Xtrabackup successfully and it works like a charm on my several CentOS / RHEL servers! Hats off to the team!

But my problem starts when I tried to install this on one of my client's Suse EL 10 server. I believe Xtrabackup is not natively supported on Suse. But being an RPM based platform, I tried installing RHEL5 rpms from percona site. But they don't work. They install (a fairly older version 1.5.x) but when run they throw the error like

Command I am running : /usr/bin/innobackupex --ibbackup=xtrabackup --defaults-file=/etc/my.cnf --user=some_user --password=password /root/backups

Error shown is : innobackupex: fatal error: no 'mysqld' group in MySQL options

I have made sure that my.cnf exist, it contains all necessary parametes like datadir / log files related parameters.

See if you can help me with this and let me know if you need any more help / logs that I can provide.


texts:
The tool ldfreader.exe has an option called -lopFilter

  -lopFilter      Dump only records with these LOP. (short form: -l)

But, I don't see anything explaining what a LOP is. What is known about "LOP"s?


texts: Design: Multiple Values in One Column or Many Columns?

texts:
I am designing a database based on NBA stats.

For a particular part, I have:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

For game score, there is quarter one to quarter four. How should I design it it to include overtime quarters. What is best design method for this situation?

Should I put the values into one column?

Or should I have a separate table as follows:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

OvertimeScore: [ GameScoreId(FK), Quarter, score ]

The method above, will include the overtime score in the table "OvertimeScore", where quarter column stores the quarter value, and score column stores the score value.

It doesn't seem right design for some reason. Please help thanks.


texts: What's the official successor for sp_resetstatus in case of a SUSPECT database?

texts:
I have a database that has been marked SUSPECT because the volume that both the data and the log file were on suddenly disappeared (an accident - this is a developer workstation). I tried bringing the database back online:

ALTER DATABASE D SET ONLINE

That did nothing. On the web the procedure sp_resetstatus is being recommended but it appears to be obsolete.

I do not wish to run DBCC CHECKDB, at least not right now. The database is very big. What is the official way to bring a SUSPECT database online as quickly as possible?


texts: Is it a good practice to isolate login information (username, password) in a separate table?

texts:
I have a database table called "patient" where I store the patient information. The structure (list of columns) is as follows.

patient table

idPatient (int)
user_name (varchar)
password (varchar)
first_name (varchar)
last_name (varchar)
height (double)
weight (double)

This database is connected to an android app remotly. There is a login process and that is why you can see user_name and password fields.

However I have an idea about the following table structure, instead of above.

user table

idUser (int)
user_name (varchar)
password (varchar)

patient table

idPatient (int)
user_iduser (int) /*The foriegn key of `user` table*/
first_name (varchar)
last_name (varchar)
height (double)
weight (double)

So which type of structure is the best? The only reason I came up with 2nd structure is because to further protect user information by not putting them in same place where it is used for login check.

Please advice.


texts: Export/Import Subset of data in postgres

texts:
Using the following query, I'm able to export two tables, selecting 500 items randomly from the first:

copy (select * from (
          select 1 + floor(random() * 1040000)::integer as "documentUUID"
          from generate_series(1, 600) g
          group by 1) r
       join "Document" using ("documentUUID")
      inner join "Citation" on ("Document"."citation_id" = "Citation"."citationUUID")
      limit 500)
to '/tmp/data.sql';

I want to import this data into my testing database, but COPY FROM doesn't seem to be able to handle multiple table imports.

How can I make this work?


texts: What's the procedure to set up username/password on mongodb?

texts:
I have tried setting up username / password .But none of the methods seems to work.

1. In mongod.conf ----> remove **#auth=true** to **auth =true**

2.Started the mongod server --> sudo service mongodb restart

3. Created the user in --> use admin -->db.createUSer()
Followed these procedure still not able to figure out how it works its not working.

mongod --auth

But then I try to connect its opening with out any username password error.

I have checked many documentations and answers but was still not able to figure out.


texts: MySQL - include a default value in LOAD XML INFILE statement

texts:
I have the follwing table:

CREATE TABLE `tab1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DEFAULT` VARCHAR(25) DEFAULT NULL,
  `ITEM` varchar(25) DEFAULT NULL,
  `TIMESTAMP` TIMESTAMP DEFAULT NULL,
  `NAME` varchar(25) DEFAULT NULL,
  `SURNAME` varchar(45) DEFAULT NULL,
  `LOADED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

I then have an XML file containing multiple entries such as:

<RECORD
  ITEM="item1"
  TIMESTAMP="2015-08-26 10:04:40"
  NAME="Bob"
  SURNAME="SMITH"
/>

I use MySQL to import the data with:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1`
ROWS IDENTIFIED BY '<RECORD>';

This works OK. However I need to specify a Default value for the DEFAULT field. But it needs to be specified at the time I load the file, and will be different for each file.
(for the sake of argument let's use the filename, myfile.xml)

How can I add a default value for this row during the XML Import?

I looked at using something like:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1` (@default="myfile.xml")
ROWS IDENTIFIED BY '<RECORD>';
SET DEFAULT=@default;

But it didn't like that. Is there a way round this?


texts:
I was asked to advise some business analyst on migration from Oracle DB 12c to 19c. The thing is I have never used or done anything related to Oracle DB. From my experience with MSSQL during upgrade I know that some features became deprecated hence it required a bit of manual work for example re write custom made stored procedures, there were some issues with indexes and etc..

The good thing with MSSQL was it told you before upgrade what issues you will encounter and what is deprecated. Can you do check it similarly in Oracle Db aswell?

Also, please share your upgrade stories, did it went smooth? Did you encountered any issues afterwards? What should be kept in mind (that might break after upgrade) before upgrading? Also I would appreciate any related articles/videos/etc...


texts: Can I get pg_rewind to show me lost data after a Postgres failover?

texts: Say I have Postgres setup with a warm standby using the build in replication features. Now, imagine a scenario where the data centre the master loses power and the slave is in another data centre and I promote it to a master. Later, the original master comes back up. At this point, I could use pg_rewind to rewind the original master into to a state where it can follow the new master. Is there any way I can see what changes pg_rewind has has undone so I could, for example, manually fix up the data by rerunning inserts against the new master if it was important?


texts: mongo db-Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2

texts:
while adding secondary member in replica set, i get following error:
"Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2"
my first machine configuration is:

second node config:


texts: Using Resource Governor to limit query CPU usage for same user

texts:
I have a remote MS SQL Server and I have several types of queries.

I run some queries that are part of a low-latency production application, and need to ensure they get domne in a timely manner.

But I also have "research" queries that tend to be CPU heavy.

How can I set the classifier such that different queries (from the same user) run with different resource pools so that my research queries don't hog all the CPU?

I am stuck on how to do this because I can't differentiate by user.


texts: Mongodb authentication database best practice

texts:
In mongodb you can store user credentials in different databases.  E.g. Say you have a products db, you could store the user products_worker in either database admin or products.

Is there a best practice here?  I don't really like having users all over the place.  Is there anything wrong with storing all users in admin?


texts: How a single column update is saved to disk

texts:
In learning about how data in a database is saved to disk, I came across the slide below. Earlier in the presentation they mention a block size is roughly 512 to 8,192 bytes. I haven't worked with files much before, I basically think about programming in terms of main memory access. But the presentation says to transfer 1 block into main memory it takes about 1ms. This is opposed to nanosecond-scale main-memory modifications on individual records.

So the slide below says that to modify a single record (a single column of a record even), you must save an entire block back to disk. I assume this is for every single change you make. So for every network request that comes into the database calling UPDATE, that is 1 block read and 1 block write (assuming it's for one record in one table). That is ~2ms per update. If you have 100k requests per second, that's 100 requests per millisecond. Assuming each request did 1 record update, that means you probably have 50 databases handling the request load.

I know my math and assumptions are probably wrong. Which is why I am asking this question:

If you really must read and write an entire block per modification. It seems like you could read just the slice you wanted instead.
How a database optimizes updates so you can fit more into that ~2ms window per update. I can imagine if you batch update a few records, they all are in different blocks, so that means even slower time.


texts: Row inserted to DB in main app is not available in the different process. Can MySQL defer some queries?

texts:
I have such problem. My main app process some request and insert the new row into DB, then send an Event to the queue. I have the different process that listens on that queue and when he get details about an inserted object and try to get it from DB, DB returns empty row. I wrote an algorithm with backoff and usually after 3 or 4 times, I manage to get that row.
And my question is, are they some default caching mechanism in MySql that cause it.

I am using Laravel 5.0 as app framework, MySQL 5.6.38 as DB and


texts: Best practice guidelines for a application table archive in Oracle 12C

texts:
Database environment: Oracle 12C Enterprise edition

I have a table  in the application schema and corresponding history table in the history schema.
The application  table has about 10 small varchar2 columns with a maximum row size of 200 bytes.
Once a week ( call it a period) an Oracle scheduler job will run a stored procedure to extract the data out of application table and insert into the corresponding history table.
The history table will have the year and period number columns in addition to the application fields.
A separate asynchronous process purges the application table after the rows are transferred to the history table (outside the scope)
The  history table will contain a year worth of data from the corresponding application table. New history table will be created each year.
The  history table data is queried primarily by the application id column  and other criteria including the year and period

At this time I am not considering a partitioned table design... With the above design goals in mind, I am considering following points in designing my tables and load stored procedures -

setting up compression at the table space level for the history schema
Direct-path  insert with append hint
Make indexes unusable before load and usable after loading

My Question:  Looking for recommendation on the design I am considering.


texts: MySQL database backup stuck on 'checking permissions'

texts:
I'm trying to backup a database on local machine. But the file is only 30MB after hours. Then I thought the hard drive corrupted or something that I tried this on another table and it worked fine.
Even MySQL workbench crashes when I connect to the database. So I used a python connector to print out the size of the databases. Sizes are in MB.
[('mysql', Decimal('18.5')),
 ('information_schema', Decimal('0.0')),
 ('performance_schema', Decimal('0.0')),
 ('sys', Decimal('0.0')),
 ('stocks', Decimal('2875.5')),
 ('test', Decimal('1183.9'))]

The stocks one is the one that got stucked on the checking permissions state. When I export the test it took only 40 mins. Then I went back again to try to export the stocks but still no luck.
I used show processlist to find out what happened
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host           | db     | Command | Time   | State                  | Info                                                                                |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
|  5 | event_scheduler | localhost      | NULL   | Daemon  | 209821 | Waiting on empty queue | NULL                                                                                |
| 77 | root            | localhost:3753 | stocks | Sleep   |   1479 |                        | NULL                                                                                |
| 84 | root            | localhost:4289 | stocks | Query   |      0 | init                   | show processlist                                                                                |
| 86 | root            | localhost:4612 | NULL   | Query   |    442 | checking permissions   | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

Well at least when I was trying to backup the test it doesn't stuck there.
I used the following command to backup the database
mysqldump -u root -p stocks > stocks.db

When I kill the process the error message was this
mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL server has gone away (2006)

The test has about 4k tables and the stocks has 20k tables. Is that what's causing this issue?
Any idea what happened? Or is there any other ways I can backup the database?


texts: Will an update in my trigger trigger itself? Oracle PLSQL

texts:
Here's my PLSQL trigger:

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG
BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN
FOR EACH ROW
DECLARE
var_root number := :new.ROOT_ID;
var_par number := :new.UNIQUE_ID;

BEGIN

UPDATE UNIQUE_PHYSICIAN
SET ROOT_ID = var_root
WHERE PARENT_PHYS_ID = var_par;

END;

Will this trigger propagate? Like, if it updates a ROOT_ID for another record, will that trigger its own trigger? Further, if it does trigger that, will it use the new ROOT_ID? I want the ROOT_ID to propagate down the tree I've built.

Edit:

How this works is that each record has a unique ID, a parent ID, and a root ID. I basically have a tree, each member of that tree has a root_ID pointing at the unique ID of the root and a parent ID pointing at the one above it. The root's root and parent IDs are its own unique ID.

in the case that a user manually changes a record to point at a new root and parent, I want all the children of that node to have the new root ID. Is there a better way to do this?


texts: Are explicit transactions needed in this while loop?

texts:
SQL Server 2014:

We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

If we let the below run for a bit, and then cancel/terminate the query, will the work done so far all be committed, or do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END


texts: How to GRANT object permission to a user defined server role?

texts:
I want to create logins that are granted permission to access

all user databases
view all definitions
select all records of all tables

To me it seems to be too much overhead to assign the login to each database and add it to a custom db role or to a default db role like db_datareader in each db.

Instead I tried to use a user defined server role.

But how can I assign the required permissions to a user-defined
server role to allow view any definition and db_datareader for all
dbs?
Is this even possible?


texts:
Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows.

select * from [IP of server].[Database Name].[dbo].[Table Name]

But I want to get [IP address] from a variable and use it.

Ex: Declare @ServIP varchar(50)

set @ServIP = 'IP of server'

select * from [@ServIP].[Database Name].[dbo].[Table Name]

I'm getting error If I use variable.

How can I do it?


texts: What is happening when Seconds_Behind_Master oscillates between two very different sets ov values?

texts:
I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the transaction server, and dbS3 was configured from an innobackupex snapshot of dbS2, which put it about 45 minutes behind dbS2 when dbS3 started replicating.

This is what a loop showing some parameters from show slave status\G looks like:

Fri Jul 31 23:46:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285744

Fri Jul 31 23:46:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285763

Fri Jul 31 23:47:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2223

Fri Jul 31 23:47:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2226

Fri Jul 31 23:48:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2227

Fri Jul 31 23:48:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284885

Fri Jul 31 23:49:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284568

So it seems that Seconds_Behind_Master on dbS3 is jumping back & forth between the number of seconds dbS2 is lagging behind dbS1 and the number of seconds dbS3 is lagging behind dbS2.

Shouldn't show slave status\G on dbS3 always show the lag between dbS2 and dbS3 without regard for how far dbS2 is behind dbS1?


texts:
BEGIN

DROP TEMPORARY TABLE IF EXISTS tblEmployeeAttendance;
CREATE TEMPORARY TABLE tblEmployeeAttendance AS
SELECT emp.emp_id,emp.emp_name,x.attendance_date,
CASE
  WHEN x.CheckIN ='0' and x.Checkout = '1' THEN '1'
  WHEN x.CheckIN ='0' and x.Checkout is null then '2'
  WHEN x.CheckIN is null and x.Checkout = '1' then '3'
  ELSE '0'
END AS Status
FROM tbl_employee emp INNER JOIN
(
SELECT emp.emp_id,emp.emp_name,att.attendance_date,
   max(case when att.inorout ='0' then att.inorout end) as 'CheckIN',
  max(case when att.inorout ='1' then att.inorout end) as 'Checkout'
FROM tbl_employee emp
LEFT JOIN employee_attendance att
ON emp.emp_id = att.emp_code
WHERE att.attendance_date >= DATE_FORMAT(search_startdate, '%Y-%m-%d')
AND  att.attendance_date <= DATE_FORMAT(search_enddate, '%Y-%m-%d')
GROUP BY emp.emp_id,att.attendance_date
) x  ON emp.emp_id = x.emp_id
GROUP BY x.emp_id,x.attendance_date;

SET @@group_concat_max_len = 75000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN calemp.calendarDate = ''',
      date_format(calendarDate, '%Y-%m-%d'),
      ''' THEN coalesce(att.Status, 0) END) AS `',
      date_format(calendarDate, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM yearly_date_calendar
where calendarDate >= DATE_FORMAT(search_startdate, '%Y-%m-%d')
  and calendarDate <= DATE_FORMAT(search_enddate, '%Y-%m-%d');

PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;
DROP TABLE tblEmployeeAttendance;
SET @@group_concat_max_len = 1024;
END

I am getting this error:

  MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1


texts: Permissions via Trusted Connection partially working

texts:
Issue on apparent partial permissions but cant pinpoint.  I have SQL-Server that allows trusted connections for Windows Authenticated users.  C#/WPF app connection no problem.

The database has some functions, stored procedures and views, all common.  Strangeness is that if the Windows login user is person "A", they get in no problem, access to what appears to be all the functions, procedures for querying report data, etc.  However, person "B" on same network and trusted connection gets error on simple function within stored procedure.

For example, I have two otherwise very simple functions and have a stored procedure/query that does something like,

select
      func1( a.SomeField ) Func1Answer,
      func2( a.AnotherField ) Func2Answer
   from
      SomeTable a
   where
      a.Column = @passedParameterToProcedure

aside from the simplicity of this example,  it works for person "A", fails for person "B".  Both users on same Windows Authentication connection to server.  Not passing explicit login user / password for connection.

Any ideas on how / where to search why this permission works for one and not another?

The error reported is:

  User does not have EXECUTE permissions on FUNC1...


texts: SSRS: Error Getting the Fields And Parameters for a shared Dataset based on a Stored Procedure

texts:
I am using SSRS and adding a shared dataset based on a stored procedure with parameters. When I go to refresh the fields so Visual Studio adds the correct parameters and fields, sometimes I get the following error message:

  Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.

The detail message is

  Procedure or function 'SP NAME' expects parameter '@parameter name', which was not supplied.

The problem happens quite randomly. Sometimes it works, other times it doesn't. Doesn't matter what is inside the stored procedure. Without the fields and parameter list, you can not add the data set to a report.

Thanks!


texts: How do you minimize downtime for an ALTER?

texts:
I've recently inherited a MySQL database that is growing. It's just over a billion records and the performance of one of the tables is abysmal. I would like to change databases eventually, but I do not have the time to do so right now.

The creator of the database decided to use BIGINTs in place of TINYINTs or SMALLINTs, chose poor indices, didn't partition the table (when it could really use one). Unfortunately, every time I try to ALTER this table, it locks it and the alter could run for days, which is something that I can't really have, because the table is constantly growing and needs to be accessible.

I'm using MySQL 5.7.5 and even if I choose an 'In Place' algorithm or no lock, it still seems to affect the performance OR it will run and never finish, like it times-out or something.

Is there a way to quickly alter a table without having much downtime?

Here is the CREATE TABLE:

CREATE TABLE `devicelog` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` bigint(20) unsigned NOT NULL,
  `system_id` bigint(20) unsigned NOT NULL,
  `device_id` bigint(20) unsigned NOT NULL,
  `field_id` bigint(20) unsigned NOT NULL,
  `val` double DEFAULT NULL,
  `valid` tinyint(1) NOT NULL,
  `timestmp` datetime NOT NULL,
  `unixtime` bigint(20) unsigned DEFAULT NULL,
  `interval5` bigint(20) unsigned DEFAULT NULL,
  `interval15` bigint(20) unsigned DEFAULT NULL,
  `interval60` bigint(20) unsigned DEFAULT NULL,
  `interval1440` bigint(20) unsigned DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `site_id` (`site_id`),
  KEY `system_id` (`system_id`),
  KEY `device_id` (`device_id`),
  KEY `field_id` (`field_id`,`device_id`,`system_id`,`site_id`,`valid`),
  KEY `unixtime` (`unixtime`),
  KEY `created_timestmp` (`created`),
  KEY `timestmp` (`timestmp`)
) ENGINE=InnoDB AUTO_INCREMENT=1012220164 DEFAULT CHARSET=utf8 COMMENT='Device log.'


texts:
How should I name my Tables when creating a new database?

Singular: Client or Plural: Clients?


texts: Seeing 2 very different execution plans for an UPDATE between test & prod environments

texts:
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)

Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.

Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?

The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...

The extra operators in the plan from Prod below (minus Clustered Index Update):


texts: Mismatched Identifiers in query for columns with same name but different casing

texts:
AWS has a service called Config that allows you to use SQL-like syntax to query your resources.  For whatever reason, they changed a field that used to be called dnsname to dNSName when going from v1 of their Load Balancers to v2.  So, I'm trying to write a query that encompasses both such as:
SELECT
  configuration.canonicalHostedZoneId,
  configuration.canonicalHostedZoneNameID
WHERE
  configuration.dNSName = 'abcd.com'
  OR configuration.dnsname = 'abcd.com'

However, I get the error
Error
mismatched identifiers in query: configuration.dNSName, configuration.dnsname

There is no FROM in the syntax because their Service only queries from one source.  When I run the same query without the OR (ie- on configuration.dNSName on its own or configuration.dnsname on its own) everything works fine.  The query seems to specifically break because of something to do with "dns" versus "dNS".  Does anyone have an idea of what is causing this?


texts: How can I insert without changing the identity seed?

texts:
I will add the records in table A to table B. The tables are the same. However, table B is an actively transactional table. When doing the operation, the record in the tableB will continue to be added row.

tableA
Id
1
2
...
1000000
1500000000
1500000001
1500000002
1500000003

tableB

Id
1000001
1000002
...
80768928

Some of the new records I will add are higher than identity seed, the seed will update to 1500000004. Is there a way to do this without updating this value?

  tableB Identity Seed : 80768928

I use these commands when doing the operation:

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFf


texts:
The table is laid out like

   name, id, attr1, attr2, data_date
    a,    1,  sals, ksdjf, 05-25-2018
    b,    1,  laskd, lskd, 05-20-2017
    c,    2,  klss,  kjfe, 03-20-2018
    d,    2,  aow , lww ,  02-10-2018
 .....

select name, id, attr1, attr2, data_date from table1
where (id,data_date) in
(select id, max(data_date) from table1 group by id)

The above query works. Essentially for each ID, I want to select the row with the max( data_date ). I am sure there is a better way with analytic functions.

Results of my query should be

name, id, attr1, attr2, data_date
a, 1, sals, ksdjf,05-25-2018
c, 2, klss, kjfe, 03-20-2018

Appreciate any help to rewrite it better. Thank you.


texts:
I want to make a SELECT * FROM of 471 strings in table rows, but is not working.

SELECT *
FROM table
WHERE user_id IN (SELECT id
                  FROM table2
                  WHERE user_id = '(bla)|(bla2)|...|(blaN)')
                    AND field3 IN (SELECT id
                                   FROM table2
                                   WHERE field4 = 'bleh');

I want to know if is correct search many strings in a same query?


texts: Parameter for Sybase Sql Anywhere 12 Serverport

texts:
If SQL Server Anywhere is started via "dbsrv12.exe -z -x tcpip " it opens a Serverport.

Default is 2638.

If more then one database is opened, the other ones are getting "some" serverports.

How could I say: open the server on "this" port

In help of SQL anywhere I find:
TCP/IP: Broadcast, BroadcastListener, DoBroadcast, Host, LDAP, LocalOnly, MyIP, ReceiveBufferSize, SendBufferSize, ServerPort, TDS, Timeout

Using dbsrv12.exe -z -x tcpip ServerPort 54382 gives 2 errors, that ServerPort and 54382 are not a file.

http://dcx.sybase.com/1200/en/dbadmin/serverport-network-conparm.html states that:

  You can specify a single port number, or a combination of individual
  port numbers and     ranges of port numbers. When you specify a list
  and/or range of port numbers, the database server attempts to bind to
  all specified port numbers.  For example: (port=1234)

  Default  TCP/IP   2638

Therfore i tried dbsrv12.exe -z -x tcpip (port=54382) and dbsrv12.exe -z -x tcpip 54382, both giving the same error as above.

In which way do I misunderstand the help?


texts: How to partition a table by timestamp, where data from every i'th day goes to partition i?

texts:
I'm looking into partitioning a table in my InnoDB database.  I have a column corresponding to a UTC timestamp, and I want to partition around that.

The basic idea that I want to capture is a partitioning scheme where, with a fixed number of partitions, we cycle through them as follows:

Given 3 partitions (3 for simplicity)

  Data with timestamp column from day 1 goes into partition 1
  Data with timestamp column from day 2 goes into partition 2
  Data with timestamp column from day 3 goes into partition 3
  Data with timestamp column from day 4 goes into partition 1
  ..... day 5 .... partition 2
  ... rinse and repeat

Basically, extract the day out of the timestamp and put the row into partition DAY MOD N where DAY is the day that the timestamp corresponds to (filtering out hours/minutes/seconds) and N is the number of partitions.

This is probably easily done, but I can't find a similar example to emulate this with.  What would the ALTER TABLE query be to partition in this fashion?

Update

By atxdba's suggestion, I tried partitioning by hash.  I tried the following statement:

ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;

This results in error code 1564: This partition function is not allowed.  Looking at this list of limitations for partitioning, it doesn't appear that FROM_UNIXTIMESTAMP is supported for partioning a table, so a different transformation from timestamp to date is required.


texts: MSSQL Logon Error in /var/opt/mssql/log/errorlog

texts:
Hello I checked in /var/opt/mssql/log/errorlog. then I get an error message like this:

The login packet used to open the connection is structurally invalid; the connection has bee
2021-01-22 16: 47: 38.21 Logon Error: 17832, Severity: 20, State: 2

this happens every day.
I've done a search on the web search but I can't find the answer.
I only get the answer for Windows users. here is the link URL.
Hopefully I can find the answer here. thanks


texts:
I am pretty new to databases and SQL.
Have following question, didn't figured out how to google this.

I am designing database structure for my application. Let's say I have following tables:

user

id (PK)
department_id (FK)
first_name
last_name

department

id (PK)
name

task

id (PK)
user_id (FK)
? department_id (FK)

Should I add department_id into task, or maybe user_id will be sufficient?
In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

UPD:

Let's assume we don't plan to change task's owner through time. I consider extra foreign keys only as shortucts to avoid extra joins while selecting data.

Example: let's assume we have one more table:

session

ID (PK)
started_time
task_id (FK)
? user_id (FK)

If I won't add user_id, I will need to perform two extra join, first to get user_id from task_id and then, to get department_id from user_id.

The question is - is this generally normal or bad practice?


texts: Partitioned Database restore is very slow on SQL Server 2012 with SP2

texts:
Team,

We have a Partitioned database which is very small in size(112MB backup size). This is partitioned with 15000 files for one table and 7500 files for other table.

Currently we are running into issues in restore taking significant amount of time which is having implications on application build process.
We tested with both Virtual and Physical hardware.

Please let us know your thoughts on the slowness? We have explored CU6 for SQL Server 2012 SP2 as well with no luck

Regards,
Sharath


texts: Can I install sql server on Win XP machine with user profile on D drive?

texts:
Is it possible to install sql server on a windows xp machine where the user profiles are created on a non system root directory, that is, on D drive rather than C drive?

I am trying to install SQL server 2008 R2.


texts:
I want to permit access to a mysql server over IPv6 and limit access to the particular account to our ULA subnet we are using (fdd7:03d7:6247::/48).

The manual seems to suggest that wildcards are valid in the host when using IPv4, but how do you do this with IPv6?

  You can specify wildcards in the host name. For example, 'user_name'@'%.example.com' applies to user_name for any host in the example.com domain, and 'user_name'@'192.168.1.%'.

I have tried all these values which seemed like they should be valid.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247::%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247:%'   IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247:%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247::%' IDENTIFIED BY  '...' WITH GRANT OPTION;

Using the % only in the host field works, so I know I have the password, and server setup properly.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'%' IDENTIFIED BY  '...' WITH GRANT OPTION;

The server has DNS resolution disabled with the skip-name-resolve, and I don't really want to enable it, and mess around with setting up reverse DNS zones for the ULA address space.

To repeat, how do I grant access for a user only if they connect from the  fdd7:03d7:6247::/56 network?


texts: does the mere existence of a primary key increase query speed for large data sets?

texts:
So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store basis and resets at midnight, but that's besides the point.

As it stands, queries on this table take upwards of one minute and I can't figure out why. I have added indexes on columns that have high selectivity ratio but it hasn't made any difference. So, if I add an auto increment 'id' field for each row, would that speed things up, or are the queries slow because there are so many columns and so much unique data?


texts:
I have a huge number of large databases (300+!) that vary in size from teeny tiny to absolutely massive (120 GB+) in size.  I've already determined a way to generate scripts to decide what indexes, foreign keys, primary keys, and default constraints need to be removed in order to convert a known set of columns from int to bigint data types.

The problem we're running into is on the larger databases, we're hitting 2-3 days runtime on this query, which isn't acceptable in production. Is there a method to make these conversions that is faster than the current drop-change-rebuild process?


texts: Which privileges for MySQL extended status?

texts: If I want to execute "SHOW STATUS" within MySQL or use "mysqladmin extended-status" on command line with a non-root user, which privileges would this user then need?


texts: Extract certain rows from table and import

texts:
I have a requirement to export / extract certain rows (matching some query) of some tables from one oracle database and import those rows in the respective tables of other database.

both source and target tables have same schema.

My question is, .Whats the best way to implement this requirement.?

Heard of oracle export data pump utility but I have to extract only certain rows.Or Is there any other way to achive this programmatically.

thanks in advance.


texts:
I don't have too much administrative experience with Oracle DBs, so forgive me if I make a fool out of myself.

In our current setup, each of our clients has a database user on our Oracle database. Most applications let them log in using this user and see only their data. However, when a support person needs to confirm an incident, he needs to impersonate the client user to get the exact same rights on the database. Currently this is being done by looking up the client's password - I don't need to explain why this is a bad idea.

Oracle has a nice "GRANT .. CONNECT THROUGH" syntax where you can give a user the ability to log in as someone else without knowing their password. The only problem is that, according to our DBAs, this permission has to be given explicitly on a per-user basis, so if we have 5 support users and 100 client users, this means we need to issue 500 commands to enable the support users to log in as any client user, which poses somewhat of a maintenance issue.

I find it hard to believe that Oracle does not support using roles for this, so you could say something akin to "let all SUPPORT_ROLE users log in as CLIENT_ROLE users". I've been looking around for a while but lacking the correct terminology I'm having a hard time figuring out whether this is really not possible.

TL;DR: Is it possible to configure Oracle's CONNECT THROUGH based on roles?


texts: History table design for supertype/subtype

texts:
I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a separate history table, but I can't decide how to track history for changes made to subtype tables.

If I use separate history tables for each subtype table I can reconstruct records by joining them with the supertype history table, except in the case where subtype history tables change independently of the supertype history table. By independently, I mean there are x updates to data in the supertype table, creating x supertype history records, and y updates to a subtype table creating y subtype history records. If the changes are made on the same day, how would I reconstruct records?

Is this a good use of supertype/subtype, or should I denormalize the tables?
Otherwise, can anyone suggest any way to approach the history issue for this type of design?

Using MS SQL Server 2008.

Here is a very simplified ERD:


texts: Are there undocumented Linked Server limitations?

texts:
I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server.  There are a few stored procedures that execute a pass-through against the linked server.  Once in a while, a few sessions hang at the very first DML statement of the entire procedure (SELECT) against the linked server, never get any result back, and hang indefinitely with the OLEDB wait type.

Upon some testing, I found that executing a very simple statement in multiple sessions against it causes a lock up of one or more sessions. A simple statement being anything that uses linked server name like...

SELECT OrderNo
FROM OPENQUERY(Linked_Server,
    'SELECT OrderNo
     FROM Orders
     WHERE OrderNo IS NULL');

and

EXEC ('SELECT OrderNo
     FROM Orders
     WHERE OrderNo IS NULL') AT Linked_Server

The strange things are that...

One of the sessions always finishes
The hang sessions usually successfully finish some random number of batches (usually below 10) before it freezes
With the same connection strings, executing the same SQL statement (without OPENQUERY because it's directly against the linked server over ODBC) in a different environment, such as VBScript using ADO, has no issues, even when the number of concurrent sessions is increased (e.g. 10 sessions w/300 executions each instead of 2 sessions w/100 executions).
Profiler shows no error.  All hang sessions just stop at one same method IDBInitialize::Initialize with no further processing.
Sage side logs also show no errors or anything out of ordinary. They look normal until the point of freezing, after which the logs look as if the Sage's ODBC driver never received the query from the hung sessions.
I can replicate lock ups at will

I researched and found this page SQL – Openquery (1) Use It Or Not Use It.  Because ADO has no issue, and for what's explained in the linked page, I have to deduce that this must be caused by some limitations of linked server.   But I can't find any documentations on that at all.

So, here are the questions.

Are there unofficial limitations to Linked Server, like the
number of connections or the total size of ALL requests/result and such?
If not, what could be the potential causes of it?
Lastly, is this a bug in 2008, and not in the newer versions?


texts: SQL Performance - One counter to rule them all - identify CPU/MEM/DISK/NET pressure

texts:
I've been performance tuning SQL servers for a few years as a DBA.

I'm trying to create a fast-food version of performance metrics that can quickly (in 5 minutes) and accurately (provable) answer a question from Management "Does this server need more/faster _ ?"

'_' being one of these 4 possible bottelenecks in an IT-stack bottom up (from server-perspective, without going in to the app/code/ui):

NETWORK
DISK
MEMORY
CPU

There are thousands of counters, articles, products to help monitor these. But is there a simple, instant and accurate script that can pinpoint if any of these 4 need to be scaled up or scaled out ?

e.g.
sys.dm_os_wait_stats - SOS_SCHEDULER_YIELD has high signal waits => need either more or faster CPU.
PAGEIO_LATCH => need more files or faster DISK

Are these 2 accurate? Does Page Life Expectancy accurately 'PROVE' the need for more memory ? What is your GO-TO script for diagnosing a performance issue ?

I've used sp_whoisactive, sp_blitz, Glenn's DMVs, Spotlight, Idera etc. but am yet to come across a script that will satisfy a CIO's question about where to spend budget money, or that will correctly blame the problems on bad code, or slow SAN, or the ISP.

Everytime any (Network/Systems/DBA/App) engineer points fingers at the other team, we have to 'PROVE' our statement, and with Virtualization and Cloud, without ideal test environments, without downtime, it's getting increasingly difficult to provably pinpoint the source of server performance issues, other than maybe Task Manager <excuse the rant>


texts:
I have a table with a number field (non-null). I have another table with a varchar2 field. Examples:

Users
UID,Name
123,Mickey
41128,Joe
43,Alice

Groups
UIDS,Name
(123,43),Children
(43),Admins
(),Deleted

I want to join the two tables where the number column is "in" the varchar2 column. As you can see, the UIDS column has been developed to look like the syntax of an "in" join. However, the following does not work:

select * from users join groups
where users.uid in groups.uids;

The number field cannot be in a string. I can cast the number field to a varchar 2 field, but it doesn't recognize the groups.uids column as a list of uids. It recognizes it as one value.

I believe that the next step I need to do is to strip the ( and ) from the uids value and then split it on the comma to create a list. Is that correct or is there a simpler way to merge these two tables?


texts: How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

texts:
There are 3 tables: user, conversation, message.

Table user:

id,
login,
last_login_time (time of last user's login),
...

Table conversation:

id,
user1_id,
user2_id,
message_last_read_id_user1 - id of last read message by user user1_id,
message_last_write_id_user1 - id of last written message by user user1_id,
message_last_read_id_user2 - id of last read message by user user2_id
message_last_write_id_user2 - id  of last written message by user user2_id,
...

Table message:

id,
conversation_id,
poster_id - user's id, who send that message,
msg,
...

The aim to select users who logged during the last hour and select for each of them amount of unread messages:

SELECT u.id, u.login, u.last_login_time,
(
  SELECT count ( * ) FROM conversation c, message m
  WHERE c.user1_id = u.id AND m.conversation_id = c.id AND
        COALESCE(c.message_last_read_id_user1, 0) < COALESCE(message_last_write_id_user2, 0) AND
        m.id > COALESCE(message_last_read_id_user1, 0) AND m.id <= COALESCE(message_last_write_id_user2, 0) AND
        m.poster_id = c.user2_id
) AS unread_user1,
(
  SELECT count ( * ) FROM conversation c, message m
  WHERE user2_id = u.id AND m.conversation_id = c.id AND
        COALESCE(message_last_read_id_user2, 0) < COALESCE(message_last_write_id_user1, 0) AND
    c.user1_id > 0 AND m.id > COALESCE(message_last_read_id_user2, 0) AND m.id <= COALESCE(message_last_write_id_user1, 0) AND
        m.poster_id != c.user2_id
) AS unread_user2
FROM user u WHERE u.last_login_time >= 1452504418;

One of unread_user1 or unread_user2 always will be 0.
Query plan is here http://explain.depesz.com/s/VT6

In that query two subqueries have cross join conversation c, message m and it's very heavy operation. So maybe someone have idea how to unite these subqueries into one?


texts: MySQL - Query records between yesterday and today but with a specific time

texts:
I seem to be having a case of the Fridays. I am new to MySQL (few weeks now) and I just can't seem to get a report automated. I need to be able to query data from yesterday 8 am until today 8 am. I am going to schedule the report to run at 10 am. What is the best way to do this? I have been running it manually with BETWEEN but this will not do. I tried to use the interval feature but no luck. I think I am almost there.
One try:
purchase_date BETWEEN '2020-11-18 09:00:00' and (DATE(NOW()) - INTERVAL 16 hour)

Another try:
purchase_date BETWEEN BETWEEN (DATE(NOW()) - INTERVAL 40 hour) and (DATE(NOW()) - INTERVAL 16 hour)

If you can just point me to the function or best option in this case, I should be able to figure out the arguments. Ideas/suggestions/thoughts? Thank you!


texts: How to join 2 tables when second table will return more rows?

texts:
im trying to join 2 tables with condition - if it matches than im expecting to get all results from another table.
Current querry that doesn't work :
SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%'
  THEN (SELECT NumerCzesci FROM PodZlozenie WHERE NumerPodZlozenia = ZL.NumerCzesci) ELSE ZL.NumerCzesci END) AS [NumerCzesci] FROM Zlozenie ZL LEFT JOIN PodZlozenie PD ON ZL.NumerZlozenia = PD.NumerPodZlozenia

Table [Zlozenie] looks like :
CREATE TABLE [dbo].[Zlozenie] (
    [Id_Zlozenia]   INT          IDENTITY (1, 1) NOT NULL,
    [NumerZlozenia] VARCHAR (50) NOT NULL,
    [NumerCzesci]   VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id_Zlozenia] ASC)
);
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-001 - WKŁADKA GÓRNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-002 - WKŁADKA DOLNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-005 - RAMIĘ ROTORA 565mm');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1');

Table [PodZlozenie] looks like :
CREATE TABLE [dbo].[PodZlozenie] (
    [Id_PD]            INT          IDENTITY (1, 1) NOT NULL,
    [NumerPodZlozenia] VARCHAR (50) NOT NULL,
    [NumerCzesci]      VARCHAR (50) NOT NULL
);
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-001 - BOK POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-002 - DEKIEL POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-003 - KĄTOWNIK')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-004 - WSTAWKA UZUPEŁNIAJĄCA')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-011 - POKRYWA DOLNA TYŁ BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-012 - PŁASKOWNIK BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-013 - WZMOCNIENIE POKRYWY')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-014 - UCHWYT')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-015 - POKRYWA DOLNA PRZÓD BA3-1')

#EDIT
Querry should return desired output as follow:
IF Zlozenie.NumerCzesci CONTAINS "ZŁOŻENIE" THEN SELECT PodZlozenie.NumerCzesci FROM PodZlozenie WHERE Zlozenie.NumerCzesci = PodZlozenie.NumerPodZlozenia ELSE Zlozenie.NumerCzesci

Desired output:
[NumerCzesci]
300-000-001 - WKŁADKA GÓRNA
300-000-002 - WKŁADKA DOLNA
300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE
300-000-005 - RAMIĘ ROTORA 565mm
300-020-001 - BOK POKRYWY GÓRNEJ
300-020-002 - DEKIEL POKRYWY GÓRNEJ
300-020-003 - KĄTOWNIK
300-020-004 - WSTAWKA UZUPEŁNIAJĄCA
300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ
300-020-011 - POKRYWA DOLNA TYŁ BA3-1
300-020-012 - PŁASKOWNIK BA3-1
300-020-013 - WZMOCNIENIE POKRYWY
300-020-014 - UCHWYT
300-020-015 - POKRYWA DOLNA PRZÓD BA3-1


texts: Why does phpMyAdmin execute all queries with an apostrophe?

texts:
For example, if I use the GUI to perform an INSERT, the console will log the following execution:

Why does each item have apostrophes (aside from NULL)?

I know that I can do a  literal INSERT via terminal or PHP without the apostrophes, especially when referencing the table or columns (and several tutorials only apply apostrophes around the insert values, not table or column references). I know that this isn't exactly a, "help me question" but I am very curious if there's a reason behind this. Thanks.


texts: What are the names of these Postgres concepts?

texts:
What are the names for the concepts highlighted in the following SQL table?  If Postgres has particular names for them then I'd prefer those, otherwise the general SQL names would be helpful.

I'm especially interested in the name for 5 because I don't really have a clue.

My guesses/intuition are

Table specification
Row
Column name
Column
Not sure.  "Field"?

The background to this question is that I maintain a PostgreSQL API for Haskell and I would like to give these concepts their correct names in my API.


texts: How to get random datetime of current day in SQL Server

texts:
I am working on a SQL query where I need to get datetime of any specific date.
For this I used the following query:

DECLARE @RandomDate datetime
DECLARE @fromDate datetime = '2018-04-07'

SELECT
    @RandomDate = (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate)
* RAND(CHECKSUM(NEWID())), 5), DATEADD(second, CHECKSUM(NEWID()) % 24000, @fromDate)))

SELECT @RandomDate

Here in the above example, I want to get datetime of date '2018-04-07' i.e 7 April. But sometime it is showing datetime of 6 april also. How to solve it?


texts: How can I get MySQL to accept both resume and resumé on a primary key?

texts:
If I add both then the uniqueness constraints fail, even though my field is in UTF-8. What can be done? I'm running 5.5.16

mysql> show create table foo_person;
+------------+------------------------------------------------+
| Table      | Create Table                                   |
+------------+------------------------------------------------+
| foo_person | CREATE TABLE `foo_person` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------+------------------------------------------------+

mysql> INSERT INTO foo_person (`name`) VALUES ('resumé');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO foo_person (`name`) VALUES ('resume');
ERROR 1062 (23000): Duplicate entry 'resume' for key 'PRIMARY'

mysql> select * from foo_person;
+---------+
| name    |
+---------+
| resumé  |
+---------+
1 row in set (0.00 sec)


texts:
I am importing a flat file into a table in DBeaver v7.0.2. When I attempt to import, it's throwing an error for date field formatted as mm/dd/yy.

The date column in the table is defined with datatype DATE.

I receive the following error:

Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1

Any ideas on why it's complaining about the date format? It appears that I do not need to explicitly specify date format in the CREATE TABLE statement.


texts:
On postgres I have data like:

id | device_model | device_type
1  | "samsung"    | 1
2  | "iphone 4"   | 1
3  | "samsung"    | 1
4  | "ipad"       | 0
5  | "ipad"       | 0

And I'm looking to get the top 5 of device_model per device_type.

for the moment I'm doing :

SELECT device_model, COUNT(*)
FROM devices
GROUP BY device_model
ORDER BY COUNT(*) DESC
limit 5;

But it doesn't render top5 for each device_type. What can I use to do this? Seems I need to do a UNION.

Can I do something like :

SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 1
GROUP BY device_model
ORDER BY COUNT(*) DESC
LIMIT 5
UNION
SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 0
GROUP BY device_model
ORDER BY COUNT(*) DESC
LIMIT 5;

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL
        ^
Query failed
PostgreSQL said: syntax error at or near "UNION"


texts: Postgresql pg_basebackup from slave didnt save WAL's

texts:
Is it possible to shoot a backup with the pg_basebackup utility from the slave server (where of streaming replication with the help of this utility pg_basebackup)?

I have a hot_standby server (streaming replication with pg_basebackup), and periodically run pg_basebackup from slave, but when I open my backup (base.tar.gz) I can't find any WAL files.

Do I have to set up archive_command on master (or slave, idk)?


texts: Average Time That an App's Client is connected to a Database?

texts:
I'm studying Tuning Aspects, and my professor told me that a good practice it's to expect some stuff, between them there is this fact of Exoext the Average time that a client will be connected.

Is there an average time that an apps is connect to a server? I heard something bout there are 2 type of client's connection, but nothing clear.

I want if it's possible something like "The average it's between 50 and 70 miliseconds" for example.


texts: Can I use Database Engine Tuning Advisor for Azure?

texts:
I am trying to use "Database Engine Tuning Advisor" for queries in Azure SQL.

According to https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor it should work with Azure SQL.

However it gives me "Failed to open a new connection".

  ADDITIONAL INFORMATION: Database Engine Tuning Advisor does not
  support Microsoft Azure SQL Database. (DTAClient)

Is it correct? I found a thread  on MSDN forums(asked at December 2016)
that confirmed that  Tuning Advisor worked with Azure in Microsoft® SQL Server® 2016 Service Pack 1 (SP1)

Is it still supported in Sql Server Management Studio v17.3 (Database Engine Tuning Advisor 14.0.1000.169), that I am using?
What should I do to enable it?

I've tried Azure Performance Insights, but it doesn't allow me to get suggestions of indexes for individual queries that I have to analyse.


texts: Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2

texts:
SYSTEM and SYSAUX Tablespace size of my DB is in the following status now:

SYSTEM  - PCT_USED= 99
SYSAUX  - PCT_USED= 95

I believe the db is in critical situation now. What can I do to bring back it to the normal condition and what are the standard ways that should follow to keep SYSTEM and SYSAUX tablespace files safe.

Any help will be appreciated.


texts: Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)


texts: Difference between surrogate key(s) and composite key(s)?

texts: I'm a beginner with databases and have been perplexed by the various definitions to distinguish these two. Various google searches have not fully clarified their differences. Please help explain them on a simple level.


texts: using multiple selects on a single table mysql

texts:
I have a table like this in my database

now what I want is to extract the sum of credit where credit_type equals V and sum of credit where credit_type equals R, then find the difference between the two(R and V). My result should contain identity_number, sum of credit where credit_type equals V, sum of credit where credit_type equals R, balance between the two sums. As an example looking at the above table identity_number 2885 should have something like this in my returned result 2885, 3000, 1560+370, 3000-(1560+370). How do I write such a query? hope you understood the problem.


texts: SQL query returns different results when index is dropped

texts:
In SQL Server 2000 SP4, I have a query

SELECT <fields no aggregates>
  FROM PS_VED_VCH_TAO5 A
    ,PS_BUS_UNIT_TBL_AP B
    ,PS_RT_DFLT_VW C
    WHERE   A.PROCESS_INSTANCE = 594170
    AND A.VAT_CALC_AMT <> 0
    AND A.VAT_ENTRD_AMT <> 0
    AND A.VAT_ENTRD_AMT <> A.VAT_CALC_AMT
    AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND A.VCHR_SRC <> 'PROC'
    AND B.VCHR_VAT_MSG_OPT = 'E'
    AND A.VAT_CALC_TYPE = 'E'
    AND A.VAT_RCRD_INPT_FLG = 'Y'
    AND A.VAT_RCRD_OUTPT_FLG = 'N'
    AND C.FROM_CUR = A.TXN_CURRENCY_CD
    AND (
            (C.TO_CUR = A.VAT_TOL_CUR_CD AND C.RT_TYPE = A.VAT_TOL_RT_TYPE)
            OR
            (C.TO_CUR = A.TXN_CURRENCY_CD  AND C.RT_TYPE = A.RT_TYPE  AND A.VAT_TOL_CUR_CD = ' '  AND A.VAT_TOL_RT_TYPE = ' ')
        )
        AND C.EFFDT =
        (
            SELECT MAX(I.EFFDT)
                FROM PS_RT_DFLT_VW I
                    WHERE I.FROM_CUR = C.FROM_CUR
                        AND I.TO_CUR = C.TO_CUR
                        AND I.RT_TYPE = C.RT_TYPE
                        AND I.EFFDT <= A.INVOICE_DT
        )
        AND
        (
            ((A.VAT_TOL_AMT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT)))
            OR
            (A.VAT_TOL_AMT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT))) * ( -1)))
        )
        AND
        (
            (A.VAT_TOL_PCT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100)))
            OR
            (A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))
            )
        )

PS_BUS_UNIT_TBL_AP has a unique clustered index on it. If the index is present, the above query results in a division by zero in

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))

despite the fact that the query explicitly excludes rows where VAT_CALC_AMT is zero.
If the index is dropped it doesn't ( and the query plan changes).
Also, if the query is changed to add a bogus condition "A.OPERATORNAME <> 'Non existent user'", it also works normally.

is this a bug in SQL or is something else going on?


texts: Got an error reading communication packets on writer instance only

texts:
So I am connecting with aurora MySQL 5.7.12-log with Django. Below (1) is the query auto-generated by Django when I hit runserver.

SELECT `django_migrations`.`app`, `django_migrations`.`name` FROM `django_migrations`;
. SELECT django_migrations.app, django_migrations.name FROM django_migrations;

The difference is the backtick only.

Query 1 (on mysql shell) gives me an error 2019-03-06T10:41:11.390834Z 13660 [Note] Aborted connection 13660 to db: '<db>' user: '<user>' host: '<host>' (Got an error reading communication packets). So I run query 2 and it runs fine.

More weirdly, the same query runs on reader instance and cluster reader instance but not on the writer instance. Also, If I reboot the db the issue won't occur for a while but will start occurring after the db keeps running for a day or so.

Can anyone help me understand what's going on and how to fix it?

Edit

adding some more info:

mysql> SELECT `django_migrations`.`app`, `django_migrations`.`name` FROM `django_migrations`;
Empty set, 25856 warnings (0.04 sec)

show i run show warnings;

And the warnings are all the same: Warning | 1287 | 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead.


texts: Secure MySQL-Server completely (disable ability to reset root-password)

texts:
is there a way to secure a mysql-server so no one can reset a password via --init-file-startup?

there should only be a:

"root" user (password not known to anyone else than person a)
"system" user (no password, full access to one database restricted to
localhost-connection)
"reader" user (password, read only access to the "one" database and
no host-restrction)

and absolutely no way to bypass this setting.

i did not find a way till now - but maybe there is a hidden one.

thank you!


texts: How to optimize this select with join and order-by?

texts:
We have two tables:

 CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` int(10) unsigned DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
....
  `subject_id` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  KEY `created` (`created`),
  KEY `text_id` (`text_id`) USING BTREE,
  KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=237542180 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

The second one:

CREATE TABLE `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` char(150) DEFAULT NULL,
  `reg_time` int(10) unsigned DEFAULT '0',
  `password` char(255) DEFAULT NULL,
...................
  `moderation` int(1) unsigned NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created` (`reg_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123585 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

messages has ~49M records, users has 13k. DB engine: Aurora(MySQL-Compatible) 5.6.10a

The terribly long request is

SELECT messages.*, users.administrator_group_id FROM messages
    LEFT JOIN users ON messages.user_id = users.id
    ORDER BY messages.id desc LIMIT 0,20

If I run this request without order by then it takes 14-16 sec. With order it takes longer than 5 min.

I considering change business logic to avoid this request and limit recordset from messages e.g. by messages date but would like to know if there is any way to speed it up on the same hardware as is.


texts: An easy way to select one db dump from multiple db MySQL dump

texts: In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need one of them to restore at the moment, is there an easy way to select the only part I need (one database)? Of course I can try to simply select the needed text from the *.sql file to perform a query in PHPMyAdmin, but it is not so easy as the file is big.


texts:
I work at a print shop that has a decent sized archive of old print jobs. Currently, to find a job we have to search the SMB share (on win2000 server), which looks through a few hundred thousand files.

Our job data is organized by: Year>Month>Customer_Name_Job#  and then the contents of the job are inside the last folder.

Is there a way to create a database so that we can query a job desc. or job number? Basically just a simple search that is faster than searching with windows search would be great. I tried the windows index service but it only finds .doc file, not folder names or PDFs.

In the future we are planning on making a SQL database where complete info will be entered when the job is created so it is easily queried in the future. I was hoping to throw together something that will make it easier to search older orders.


texts: For a InnoDB only DB, which of these elements can be removed?

texts:
So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming InnoDB.  So of this list of configs, which are irrelevant if you're building for InnoDB.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
open-files-limit = 4096

[mysqld]
port = 3306
user = mysql

default_storage_engine
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
basedir = /usr
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
local-infile = 0
automatic_sp_privileges = 0
safe-user-create = 1
secure-auth = 1
secure-file-priv = /tmp
symbolic-links = 0
key_buffer_size = 32M
myisam-recover = BACKUP,FORCE
concurrent_insert = 2
max_allowed_packet = 16M
max_connect_errors = 1000000
datadir = /var/lib/mysql
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 1
query_cache_size = 0
query_cache_limit = 8M
query_cache_min_res_unit = 1K
default-storage-engine = InnoDB
thread_stack = 256K
thread_cache_size = 128
max_connections = 128
open_files_limit = 65535
skip-locking
skip-bdb
server-id  = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_cache_size = 256K
sync_binlog  = 256
expire_logs_days  = 14
max_binlog_size = 1G
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
max_user_connections = 150
key_buffer = 16M
key_cache_block_size     = 4K
bulk_insert_buffer_size  = 8M
innodb_buffer_pool_size  = 6G
myisam_sort_buffer_size  = 64M
join_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 64M
table_cache = 4096
table_definition_cache = 4096
table_open_cache = 16384
optimizer_search_depth   = 4
collation-server = utf8_general_ci
interactive_timeout = 400
wait_timeout = 300
connect_timeout = 10
thread_concurrency=8
back_log = 2048
open-files = 10000
query_prealloc_size = 65536
query_alloc_block_size = 131072


texts: Adding a database to existing distributed availability group

texts:
I am working in a test situation and have already successfully created a distributed availability group with automatic seeding. I cannot figure out how to add another database to the distributed AG. I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG. I get the following error:

  Msg 15151, Level 16, State 1, Line 28
  Cannot alter the availability group 'xxxx', because it does not exist or you do not have permission

Attempting to modify the permissions for my group login yields the same message, yet the group is under the sysadmin role. Surely there is a way to add a database after you create the distributed AG?


texts: Is Circular replication recommended among 4 MySQL servers

texts:
We have 4 MySQL servers and our main motto is that data should replicate across all the 4 servers if one is getting updated and the same changes has to go to all other
3 servers.

And below are the few other requirements

1) Changes need to be on all 4 servers.

2) one should be active at anytime which should be on Read/Write and rest of 3 to be on read-only mode.

3) If switch/Failover happens the new master should only be read/write and other servers has to be on read only

We want to achieve the above explained funtionality through the Circular replication mechanisum? Is it possible?

If not possible then what would be the recommended HA setup solution to meet above said requirement.

Appriciate any help or suggessions on this.


texts: Concurrent backups (full and transaction) and testing recovery model

texts:
My intent is to test my recovery model and setup log shipping to replicate a database on another server.

I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs a full backup on this database every morning @ 6am and on the average takes 3 1/2 hrs (210 minutes) to complete. A separate job runs every 15 minutes and performs a log backup on the same database.

So, I want to restore a full backup from earlier that morning and then all the log backups since that full backup completed how do I know which transaction log backups to select? Is there any danger to applying transaction log backups from 915am to present?


texts: Shrink database after removing extra data

texts:
We have a need to fit a database in 4GB in order to use SQL Server Express Edition. I started from a 7GB database, and found a lot of unneeded records, and deleted them. After shrinking, the database size is 4.6GB, and 748 MB is free (according to database properties).

However, when I execute exec sp_spaceused, I have interesting results:

DatabaseName    Database_size   unallocation space
xxxxxx          4726.50 MB      765.42 MB

Reserved        Data          index_size      unused
3899472 KB      1608776 KB    1448400 KB      842296 KB

Any ideas, how can I release at least some of this unused space?
Also I know table, which occupied it.

Update: is it worth to trying to rebuild table indexes?

ALTER INDEX ALL ON Production.Product REBUILD


texts:
I installed MS SQL Express 2016 edition and when I log in it, it gave me following error.

TITLE: Connect to Server
------------------------------

Cannot connect to 192.168.50.173\KONSYS.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

SQL Server also running and it can see in services.

I also add rules for 1433 and 1434 ports in Windows Firewall.

How to solve this problem


texts: Modelling a user-defined filter set in SQL

texts:
In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data.

Now, my users also need to be able to create filter sets of alerts, which define some sort of problem with the data that they need to detect. For example this might be "select all points where data.value is less than quality 50", or "select a group of consecutive points that all have the same sign", which can be answered with SQL queries. The results of these queries need to always stay up-to-date as new data rows are added to the database.

As far as I can tell, I could model this two ways:

I treat each filter as a row in a filter_definition table that somehow stores the query (as a string), and then also have a filter_result table that is periodically updated by running the filter_definition query (e.g. whenever a new data or filter_definition is created). By having my filters as rows in the database it's very easy for me to create/update/delete filters using my ORM. However this is highly inefficient to update, and also filter_result can become out of date if not updated recently enough.
I treat each filter as a view in the database, each with their own associated SELECT statement. This would be much more efficient, since nothing needs periodic updates, because the view will automatically run the query when accessed. However, allowing my users to create/update/delete a view is much trickier because it's actually deleting an entire database entity each time. In addition, querying all filters (to find all dangerous data points) becomes nearly impossible. I can union all the views for one user, but how do I know which view belongs to each user? Each view is a database entity, not a row, so it can't have a foreign key.

There might be some other solutions I haven't considered as well.

Does anyone have any advice as to how I should solve this issue?

Note: I'm using sqlalchemy as a DBMS-agnostic ORM, so really I want a solution that will work on any DBMS. However Postgres, MySQL and SQLite are my 3 priorities to support.


texts:
I have to run an UPDATE statement on live online database with 500k records. I want to know which statement will run faster:

Update Table set REC_ID = isnull(REC_ID,'')

Update Table set REC_ID = '' where REC_ID is null

i am using Microsoft SQL Server 2012


texts: Is it possible that the old primary follow the new secondary?

texts:
I need to change the roles between two nodes.

Circumstance:

2servers:

primary: psql01 and
standby: psql02

After psql01 stops, psql02 becomes the new primary. After a while, start psql01

On psql01:

[-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show
 ID | Name         | Role    | Status               | Upstream | Location | Priority | Timeline | Connection string
----+--------------+---------+----------------------+----------+----------+----------+----------+---------------------------------------------
 1  | psql01       | primary | * running            |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
 2  | psql02       | standby | ! running as primary |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

WARNING: following issues were detected
  - node "postgresql02" (ID: 2) is registered as standby but running as primary](url)

On psql02:

repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

     ID | Name         | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
    ----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------
     1  | psql01       | primary | ! running |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
     2  | psql02       | primary | * running |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

    WARNING: following issues were detected
      - node "psql01" (ID: 1) is running but the repmgr node record is inactive

I try to put my old primary to standby with the following commands:

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf node service --action=stop --checkpoint
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "sudo systemctl stop postgresql-11"

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf -d 'host=psql01 user=repmgr dbname=repmgr' node rejoin
ERROR: connection to database failed
DETAIL:
could not connect to server: Connection refused
        Is the server running on host "psql01" (172.16.11.171) and accepting
        TCP/IP connections on port 5432?

What's exaclty happen? If psql01 goes down how can I change the role to secondary?


texts:
As I know, trigger isn't supported in VoltDB. If anyone has updated news about trigger on VoltDB for nowadays, would you please let me know.

So my question: is it possible to implement a trigger-style action in VoltDB?

What I need is like following:

Once I commit an operation in VoltDB, like inserting a tuple, updating a tuple or deleting a tuple, a C function or executable binary could be executed, and the old/updated tuple value could be passed to the function.

Thanks in advance!


texts: Oracle 11g Select query returns NO ROW SELECTED but the table actually has data

texts:
In my production, There is a table named ABC under X schema. I have granted SELECT privilege on ABC table to another user named Z. When I execute the query

SELECT COUNT(*) FROM X.ABC;

as user Z it gives the following result

But when I query the same command

SELECT COUNT(*) FROM ABC;

as user X, it shows:

Even if I query SELECT * FROM X.ABC; as the Z user it shows no row selected where I get the full data from X user.

What might be the reason of this issue?

The DB is ORACLE 11.2.0.1.0 Enterprise Edition.
This DB has also Database Vault enabled.


texts:
I am failing to create a rule which will return the insert's data.

The table is:

CREATE TABLE foo(a int, b text);

My attempt for the rule is:

CREATE RULE return_data AS ON INSERT TO foo DO RETURN *;


texts: Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

texts:
Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space.

Have option to either optimize the table, or dump the table and restore it.

For restoration I will be splitting the dumped sql file into multiple files and will run parallel restore upto the point the CPU is not blocking much on DiskIO, to restore at the best possible rate.

Now, from mysql's point of view alone, which would be faster, and why?

I understand that DiskIO will be an important factor, but I want the technical points from mysql's perspective.

NOTE: The disk on which mysql is, its a high performance SSD disk.


texts:
I've got this table:

CREATE TABLE clients (
  id       BIGSERIAL NOT NULL,
  company  CHARACTER VARYING(255) DEFAULT '' NOT NULL,
  email    CHARACTER VARYING(50) DEFAULT '' NOT NULL,
  city     CHARACTER VARYING(80),
  postcode CHARACTER VARYING(20),
);

I got a list of cities and postcode. I need to find if any row matches with the data I have:

City,Zipcode
Lansdowne,2163
Villawood,2163
Smithfield,2164

I'm using this query; how can I achieve the expected result?

SELECT
    t1.id,
    t1.city,
    t1.company,
    t1.postcode
FROM
    clients t1
LEFT JOIN
    clients t2 ON t2.city = t1.city
LEFT JOIN
    clients t3 ON t3.postcode = t1.postcode
WHERE t2.city IN ('Lansdowne','Villawood','Smithfield',)
AND t3.postcode IN ('2164','2163','2163',)

Would the query be right?


texts: Select and Update MAC Addresses to Same Format

texts:
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;

+-------------------+
| macaddress        |
+-------------------+
| 90CCAADD3341      |
+-------------------+
| 90:3f:ff:11:22:33 |
+-------------------+
| 33:44:aa:bb:34:6a |
+-------------------+
| 8801abcd3231      |
+-------------------+

Is there any way I can bulk update all rows in this column so they are in the same format?

I'd like them all to be uppercase with no colons, see below;

+--------------+
| macaddress   |
+--------------+
| 90CCAADD3341 |
+--------------+
| 903FFF112233 |
+--------------+
| 3344AABB346A |
+--------------+
| 8801ABCD3231 |
+--------------+

How can I achieve this?


texts: How to do partition on a non clustered index column?

texts:
I have table ORDERS .Where there are Million rows in it .I need to add Pationed Index on Order to store data in one of two files based on the new IsLocked field.But when i checked table there is a Non clustered index on ISLocked Column.IsLocked field consists of 1 and O's.Can you guys suggest me best approach.

CREATE TABLE [Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderNo] [varchar](50) NULL,
    [AccountNo] [varchar](20) NULL,
    [CompleteDate] date null,
    [LastUpdateDate]) date null,
    [AccountID]  int NULL,
    [IsWarranty] [bit] NULL,
    [IsEmailAddressFound] [bit] NULL,
    [IsLocked] [bit] NOT NULL,
    [LinkOrderID] [int] NULL
)

But already there is a

CREATE NONCLUSTERED INDEX [IX_IsLockedAccountID] ON [coe].[Orders]
(
    [IsLocked] ASC,
    [AccountID] ASC
)
INCLUDE (     [OrderNo],
    [CompleteDate],
    [LastUpdateDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Orders    IX_IsLockedAccountID    NONCLUSTERED    72057594556710912    1    1146462

Can you giys please help me how to do partioning as i am completely new to Table partioning .


texts: Is it fine to run a public website on the sql express db?

texts: Is it fine to run a public high traffic website on a sql server 2008 express db?


texts: Does `Seconds_Behind_Master` show exact Slave Lag from Master?

texts:
Does Seconds_Behind_Master always show the correct number of seconds the slave is behind the master?

I have also Implemented the mk-heartbeat replication monitoring.

It was showing the other result in comparison with Seconds_Behind_Master.

Which one is more accurate?


texts:
I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group:

CREATE GROUP data_viewers;
CREATE USER <user> PASSWORD '<password>' IN GROUP data_viewers;

Now I would like to allow this group to be able to read data from any table:

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO GROUP data_viewers;

The command returns GRANT. Now when I connect to Redshift as my newly created user and issue SELECT * FROM something.something; I get:

  permission denied for schema something

I tried granting permissions to something: GRANT SELECT ON ALL TABLES IN SCHEMA something TO GROUP data_viewers; but this has not changed anything.

How can I allow users from my group to SELECT data from any table in the schema?


texts: Do I need to use -f to fix insufficient max server memory settings?

texts:
SQL Server Version:

SELECT @@VERSION;

/*
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)
*/

Issue:

So, I was playing around with max memory and I accidentally set max memory to 128MB and now, I cannot open a connection to SQL Server. When I tried to open a session and fix the mistake, I got this wonderful pop-up.

In order to save myself, I decided to add the -f parameter to SQL Server and bounce the service.

See more about that here:

Server Memory Server Configuration Options

and here:

Database Engine Service Startup Options

Once the service was re-started, I tried once again to connect. I got the following message:

This was expected according to the information I had about the -f flag. I then was able to open a connection using the Dedicated Admin Connection (DAC). I was then able to run sp_configure and alter the max memory setting. Here is what I saw in sp_configure before I made a change. It appears that the running value is again infinity (or close enough).

I ran this update to increase the max memory to 3GB:

USE master;
GO
sp_configure
      'max server memory (MB)'
    , 3072;
GO
RECONFIGURE;
GO

And then I removed the -f parameter and re-started SQL Server. At that point, the server became accessible in multi-user mode and I had a max memory value equal to 3GB. This is not the most elegant solution I have ever encountered; but, alas it worked; so I decided to share it here.

Does someone have a quicker and cleaner solution?


texts: CROSS APPLY-ing a parameterised function

texts:
Given the following definitions:

CREATE TABLE MyTable
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

CREATE FUNCTION fn_MyFunction
(
    @Param1 int,
    @Param2 int
)
RETURNS TABLE AS RETURN
(
    SELECT @Param1 * 2 AS 'res1', @Param2 * 4 AS 'res2', @Param1 AS 'col3'
)

I am trying to join the function to the table so that I get a calculated value (s) per row. Example: In a bookings table, I need to get the price based on a start date and end date.

Here is an example query:

SELECT      tbl.col1
,           tbl.col2
,           tbl.col3
,           fn.res1
,           fn.res1
FROM        MyTable tbl
CROSS APPLY fn_MyFunction(tbl.col3, tbl.col4) fn
WHERE       fn.col3 = tbl.col3

While I think I am getting the correct results in my actual query, how does the CROSS APPLY get the results in this case? Does it get them row-by-row (effectively)? Or does it work in a similar way to CROSS JOIN (ie: making a Cartesian Product)?


texts:
Got 2 Servers in Scale out deployment and connection is through a load balancing URL.

1) SSRS01
2) SSRS02

Everything works fine when the session is running from SSRS02 but keep getting the error from the screenshot below when trying from SSRS01.

I have looked at permissions. They are exactly the same. I can't find anything relevant in the report server logs too.

Any help on this would be really helpful .


texts: SQL Server 2014 Developer on Windows 10?

texts: Has anyone tried to use SQL Server 2014 Developer Edition (64 bit) on Windows 10 yet? I have a Lenovo G50-45 laptop that came with Windows 8.1 and I have the option to upgrade, but I don't want to shoot my working setup in the tail by upgrading -- unless it is pretty certain to be successful and reasonably bump free. I guess I'm hoping to hear from someone who took the plunge who can report unparalleled success (or abject failure) to help nudge me off the fence on upgrading Windows.


texts:
I want to add the below constraint to an existing table:

ALTER TABLE ONLY daily_summary_202004
  ADD CONSTRAINT odo_no_overlapping_202004
  EXCLUDE USING gist (((vehicle_gid)::text) WITH =, numrange(odo_start, odo_end) WITH &&)
WHERE (start_date >= '2020-04-01'
   AND start_date < ' 2020-05-01'
   AND active) ;

But there are few entries in the table that fail the constraint in production.

Is there an SQL query that I can write to get all the faulty rows so I can delete them before I add the constraint?

The DDL for the table

create table if not exists ifta.daily_summary_202004
(
    id                 uuid           default uuid_generate_v4() not null primary key,
    distance           numeric(10, 3) default 0                  not null,
    fuel_code          varchar(128)                              not null,
    state              varchar                                   not null,
    odo_start          numeric(10, 3)                            not null,
    odo_end            numeric(10, 3),
    start_date         timestamp                                 not null,
    end_date           timestamp,
    vehicle_gid        uuid
);

Essentially, there should not be overlapping odo_start and odo_end entries, hence the constraint. But because of an existing bug, we've some overlapping entries, I want to find them and get rid of them.