texts: CSV File Into MYSQL Merging - LOAD DATA on Duplicate Key?

texts:
Hey Dba Stack Exchange,

My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only 4411 unique key rows. Rest have the same primary key and thus are skipped. I cant replace into because all the columns are present in the csv. I need to do (in mongodb terms Upsert).

Basically my question is what is the best method to update the existing columns like a ON DUPLICATE KEY during an insert for bulk csv file loading.

Database is Mysql

Table1 {

userID VARCHAR(50) PK,

UnixTimestamp VARCHAR(100),

likesCars TINYINT(1),

likesTrucks TINYINT(1),

HasMorgage TINYINT(1),

... 1000 more

}

Data:

2323232,1111111,1,0,0,0,1...

2323232,2333222,0,0,0,1,0... (needs merge)

1121121,222222,0,1,1,0,0...

goes on and on haha.

Was thinking maybe since they are mainly tiny ints changing

Load Data InFile CSv... (userId, @unixTimestamp, @likesCars, @likesTrucks...)

SET unixTimestamp = @unixTimestamp

SET likesCars = @LikesCars OR LikesCars

SET likesTrucks = @LikesTrucks OR LikesTrucks

Am I on the right track here?


texts: MongoDB ixscan seems to be scanning more objects than expected

texts:
I'm using MongoDB 2.6.12 and a collection has the following index {a: 1, d: -1}. In case the types matter a is a Number and d is a Timestamp.

From what I can see in the code we are limiting this query to 21 documents. However, the query log below suggests that the index was used but it still scanned 903483 documents.

2016-05-27T22:13:05.722-0700 [conn890] query my.collection query: { $query: { a: 12345 }, $orderby: { d: -1 } } planSummary: IXSCAN { a: 1, d: -1 } cursorid:312034207299 ntoreturn:21 ntoskip:903462 nscanned:903483 nscannedObjects:903483 keyUpdates:0 numYields:89 locks(micros) r:3550704 nreturned:21 reslen:1028 1820ms

We are using pymongo 2.8.1 and mongengine 0.7.9

Can anyone explain why and if we are actually scanning that large number of objects in this query and what I should look for to solve the problem?


texts: Archiving MSSQL Database and Retrieving the archived data for reporting

texts:
I am still an amateur when it comes to MS SQL Server.

I have been tasked with Archiving our Production Database which is growing daily.  Mainly I am looking at the transactions tables, simply because they are the ones that generate the most data (average of a million records per day).

I understand that the tables from which they pull data from might need to be archived as well for referential integrity purposes.

I also need a way to be able to query this archived data whenever needed as quickly as possible if there's a report needed. Any advice?


texts:
CREATE TABLE info(
home CHAR(10) CHARACTER SET latin1 NOT NULL,
id TINYINT UNSIGNED NOT NULL,
galaxy TINYINT UNSIGNED NOT NULL,
galaxy_pos INT UNSIGNED NOT NULL,
events TEXT NOT NULL,
UNIQUE KEY(galaxy, galaxy_pos, id), PRIMARY KEY(home, id)) ENGINE = INNODB

10+1+1+4+2 = 18 bytes + TEXT, are needed for the data. How to calculate the space I need for the indexes?


texts: Is this amount of disk IO expected with MySQL?

texts:
I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage).  The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 million IO requests.  This seems abnormally high considering there is one 2 MB database used for Wordpress.  I've used the site very lightly, just some editing in the backend, no live traffic.  Here is the memory available:

# free -t
            total       used       free     shared    buffers     cached
Mem:        615376     531848      83528          0       2296      58864
-/+ buffers/cache:     470688     144688
Swap:            0          0          0
Total:      615376     531848      83528

If I have around 83 MB of free space in memory why would MySQL not keep the 2 MB database there?  The reason I suspect MySQL for the high IO activity is when I run iotop mysqld always shows having the most activity.  Here is my config:

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
query_cache_type=1
query_cache_limit=2M
table_cache=1024
join_buffer_size=4M
thread_cache_size=128
tmp_table_size=64M
max_heap_table_size=64MB

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Is there any way besides iotop to know if MySQL is using memory vs disk?  Does anything in my configuration look wrong for my system?

Update: Ran query from answer below and output was:

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| InnoDB         |             1.469 MB |             0.625 MB |             2.094 MB |
| Total          |             1.469 MB |             0.625 MB |             2.094 MB |
+----------------+----------------------+----------------------+----------------------+

In /var/lib/mysql:

df -h .

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1            7.9G  3.8G  4.1G  49% /

ls -l

total 28688
drws--S--- 2 mysql arithmakids     4096 Dec  5 05:07 arithmakids
-rw-rw---- 1 mysql mysql       18874368 Dec 13 23:03 ibdata1
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile0
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile1
drwx------ 2 mysql mysql           4096 Apr 18  2012 mysql
srwxrwxrwx 1 mysql mysql              0 Dec 13 22:54 mysql.sock
drwx------ 2 mysql mysql           4096 Apr 18  2012 performance_schema
drwx------ 2 mysql mysql           4096 Apr 18  2012 test

Based on this what would be good settings for my.cnf?


texts:
We have DatabaseA that has filestream files in it. We then have an archive process which first deletes files in DatabaseB (different server) and then inserts new files from DatabaseA into DatabaseB via SSIS.

Each night we try to backup the database and we get the following error:

  The operating system returned the error '1392 (The file or directory is corrupted and unreadable.)' while attempting 'FindNextFile' on \foldername guid.
  [SQLSTATE 42000] (Error 3634)
  BACKUP DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013). The step failed.

When I look at the file path and folder name, the name is not the same as the folder name guid reported in the error. When we reorganize the index on the filestream guid column, we are able to backup the database. How do we recover from this corruption? DBCC checkdb? Is there something else we should be doing with filestream enabled database backups or the way that we are deleting the rows with the filestream column?

We are using native SQL Server

Backup command:

  BACKUP DATABASE
  to disk = '\share\backupshare\databasename\databasename.bak'
  with noformat, init,
  name='databasename-full database backup',
  skip, norewind, nounload, compression, stats 10, checksum


texts: Equi Join On Union- and Intersection-Compatible Relations

texts:
Suppose that I have two union- and intersection-compatible relations; i.e., R1(A,B) and  R2(C,D) as:

Now , I want to find the join of both relations with equality conditions A=C and B=D.

I have taken the cartesian product of both relations and then picked those tuples which satisfy the equality condition. So, I get the resultant relation as:

Since it is giving the intersection of both relations and both are union- and intersection-compatible, so, can I write resultant relation as:

According to me ,resultant relation after equi join of R1(A,B) and R2(C,D) contains four attributes and intersection of R1(A,B) and R2(C,D) contains two attributes. So, both resultant relations are different. So, we can't write equijoin of R1(A,B) and R2(C,D) = intersection of R1(A,B) and R2(C,D) here.

Am I right?


texts: How to prevent PostgreSQL from automatically rounding numeric types?

texts:
I have a simple schema and query for a MWE.
Schema:
CREATE TABLE ttable (
  tcol Numeric(19,4)
)

Query:
INSERT INTO ttable (tcol) VALUES ('123.45678');
SELECT * FROM ttable;

Fiddle
Result:
123.4568
In this case, I have lost precision. I entered five decimal places, but Postgres automatically rounded down to four. I want this to be an error, or at least a warning of some kind that I can detect, so that I can tell the user about the loss of precision.
How can I make this happen?
Edit:
This question is obviously not a duplicate of this question. In that question, the user is using a client application which is rounding value below the stored precision. My question is about Postgres itself rounding the data to fit, not the client displaying. This would be obvious to anyone who looked further than the title of both questions.


texts: mysql: free space after drop table with innodb_file_per_table=False

texts:
how to reclaim free space after drop table?  the innodb_file_per_table=False;
and i'm using version:
Server version: 5.5.44-MariaDB MariaDB Server


texts: ALTER USER postgres PASSWORD null; now has locked me out of psql

texts:
I have installed postgresql version 9.3.14 on High Sierra via HomeBrew. Upon trying to access the server via psql, I was being prompted for a password for the user postgres. Using postgres as the password got me into the database, but I was confused, as I have never been prompted for a password for the default user before. I looked around online on how to circumvent this, and I tried the solution suggested by lalligood on remove password requirement for user postgres which was to run alter user postgres password null;
and now I cannot access psql. Trying postgres, null or omitting a password does not get me in, and I do not know what to do.


texts: How can I generate an Entity-Relationship Diagram and SQL DDL statements from natural language text?

texts: I am using LaTeX or another text-macro tool/library. How can I (1) use natural language text to describe relationships, then (2) generate an Entity-Relationship Diagram and (3) generate SQL DDL to create the database schema from it?


texts: Allow multiple users to see procedures and functions

texts:
I have GRANT ALL PRIVILEGES ON `test1`.* TO 'admin'@'%', GRANT ALL PRIVILEGES ON `test1`.* TO 'devel'@'%' (from SHOW GRANTS FOR some_user) and I have created simple procedure as admin in test1.

And here is what I see:

as admin

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| admin@%        |
+----------------+
1 row in set (0,03 sec)

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| begin
end          |
+--------------------+
1 row in set (0,04 sec)

mysql> call test();
Query OK, 0 rows affected (0,03 sec)

as devel:

mysql> select current_user();                                                   +----------------+
| current_user() |
+----------------+
| devel@%        |
+----------------+
1 row in set (0,03 sec)

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0,03 sec)

mysql> call test();
Query OK, 0 rows affected (0,03 sec)

How can I allow multiple users to see code of procedure or function?


texts: SQL 14 AG go into read only when cluster is down, how?

texts:
Looking at SQL 14 AG and trying to get this to work

https://blogs.technet.microsoft.com/dataplatforminsider/2013/06/27/alwayson-in-sql-server-2014-ctp1/

Increased availability of readable secondaries in multi-site environments

Readable secondary replicas now allow read workloads to run without any disruption even in the presence of lasting network failures or loss of quorum on the underlying Windows Server Failover Cluster.

This is especially desirable in large-scale geo-distributed environments where network disconnections are not that unusual. This is depicted in the picture below, reports running on readable secondaries far from the main data center will continue running despite a network disconnection. Similarly, new reports started during the network disconnection will run.

--
We spent a good bit of time trying to get this to work with a two node clustered instance as primary and then a third node with a stand alone instance as the readable secondary. We can't find too much more info on it other than this link. Since it's from a CPT we're wondering if it's even in the software? Or what we're missing to get it to work. Any thoughts or help would be awesome.

Thanks


texts: How to reset a postgres database that has used all of the available disk space?

texts:
I have a small ~10G PostgreSQL 8.4 database used for development that has filled up the /var directory and is now unusable. I am unable to connect to the database in this state to delete any data or drop any tables.

I am not concerned about keeping any of the data in the database, but I am unable to run a DROP DATABASE or similar command because I am unable to connect.

What is the best method for 'resetting' my database given this situation?


texts:
I have a folder containing the snapshot taken from a database.

They are all in the format organisation_product_tablename.sql.

How can I restore them back into the database?

EDIT 1:

I managed to restore all the files, but one with:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u root -p databaseName

The only one that is giving problems fails with an this error:

ERROR 1449 (HY000) at line 346 in file: 'org_sugar_routines.sql': The user specified as a definer ('root'@'%') does not exist.

This is the Head of the file:

USE `organisation_product`;

-- MySQL dump 10.13  Distrib 5.6.13, for Win32 (x86)
--
-- Host: 10.10.0.10    Database: organisation_product
-- ------------------------------------------------------
-- Server version     5.1.60-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Temporary table structure for view `vw_contact_insurance`
--

When I checked that line in the file I found that in the file marked as error there is this:

/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */

When I run this query

mysql> SELECT USER(),CURRENT_USER();

I obtain this:

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.02 sec)

Isn't this the root user? I have it as the admin user in the DB. So, what's the problem here?


texts: File size when extracting from SQL Server to CSV

texts:
I am attempting to extract a table to CSV using BCP from SQL Server 2014 and the size of the file once the data is extracted is actually larger than the size of the database itself.

One thing to note is that one of the fields in this table is an nvarchar(max) and could contain values that exceed 8000 chars.

I was just wondering if this was normal behavior when extracting this type of data to file, or whether there was some trick that I could use to shrink the file size without any data loss.


texts: 2 Date Ranges - How many days from one range excluding weekends overlap

texts:
First - Thanks for your help.

I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends.

Example Data:

Training Dates - 10/16/2014 - 10/28/2014
Fiscal Month Dates - 09/22/2014 -10/21/2014

Number of Days the training overlaps the Fiscal Month Excluding Weekends: 4 Should be the Answer (16th, 17th, 20th, and 21st)

Again Thanks for your help!!


texts: PostgreSQL: remaining connection slots are reserved for non-replication superuser connections, too many clients already

texts:
We have web application using Golang, PostgreSQL, and sqlx (adapter, connection pooler), that each request requires 1 to 8 queries, sometimes 1 transaction with 5-8 select and 5-8 insert queries.

We already set our PostgreSQL's max_connections from 1024, then 4096, but it start swapping, so we reduce them back to 64 (this is the limit before swapping).

Our RAM is 2GB, we configure the PostgreSQL's working_mem to 16MB and temp_buffers to 8MB, shared_buffers to 800MB, effective_cache_size                to 1536MB (this configuration doesn't swap at all).

There are about 18 request per second everyday at 00:00 to 02:35, and there's about 8 errors per second, at that point only 10 transaction per hours that succeed, others are failed, with error message either:

remaining connection slots are reserved for non-replication superuser connections
too many clients already

How to overcome this problem other than caching the read-only page (since the page can be updated about 10 times per minutes) or upgrading the machine?


texts: How to design relationship for racing team trades?

texts:
I'm working on a fantasy racing team project. I'm having trouble laying out the schema when it comes to the team table and the trades. I think I have the race, driver, and result tables laid out okay:

Each team has to have 10 drivers; two in group one, three in group two, and five in group three.

Each team may have two trades throughout the season. The traded and obtained columns are there so I can calculate who is on a team at a specific race (or point in time). For example, if there were three races and driver_1_1 was traded away after the first race, the team would only get that driver's points for the first race. For the last two races they would get points from the driver that driver_1_1 was traded for.

I have a strong feeling that the team table can be done in a much better way, but I'm having trouble wrapping my head around it, especially because of the trades. Please help me normalize!

If it matters I'm planning on using the latest Django Python and PostgreSQL.

EDIT:

Here's a simplified version of the schema without all of the fluff. I've also gotten rid of the hard coded driver columns and replaced them with a pick table. There's still the matter of the trades, and whether or not this is the right way to go about something something like this. Better?


texts: How to implement many-to-many relation between three table?

texts:
I have these tables

StudentInfo:

  subjectId(primary key)
  name
  location

SubjectInfo:

  subjectId(primary key)
  subjectName
  totalmarks

TeacherInfo:

  teacherId(primary key)
  teacherName

Grades:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  marks
  attendence

Junctiontable:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  professorId(foreign key from professorInfo)

There is a many-to-many relation between student, professor, subject.

That is

a student can have many subject to enroll with and can be taught by many
a teacher can teach many subject and many student
a subject can be enroll by many student and can be taught by many teachers

Questions:

Is that junction table correct or should I have 2 junction tables?
What is the SQL query to find the report for student on the basis of roll no? The detail in report should be subjectname, subjectmarks, subject, attendence, professor name who taught this subject

The query that I tried returns redundant data.

select
    teacherInfo.teacherName,
    grade.marks,
    grades.attendence,
    subjectinfo.subjectName
from
    teacherinfo
join
    junctiontable on junctiontable.teacherId = teacherInfo.teacherId
join
    studentinfo on studentinfo.studentId = junction.studentId
join
    grades on grades.studentId = studentinfo.studentId
where
    studentinfo.studentId = 1;


texts: Is it possible to quickly create/restore database snapshots with PostgreSQL?

texts:
First of all, I'm a developer, not a DBA or sysadmin; please be gentle :)

I'm working on an application workflow where a single user action will trigger complex changes in the database - creating hundreds of records in some tables, updating hundreds of records in others, etc. All in all, about 12 tables (out of ~100) are touched by this action. Due to the complexity, it's very hard for me to manually revert all of the changes before I can run another test. During most of my development time, I can simply insert a "ROLLBACK" statement near the end of the workflow, but when I get close to commiting my changes, I need to test the real thing.

I have a local copy of the production database to work with. In my case, dumping and restoring between tests is faster than writing a script to undo all the changes. It's faster, but it's still slowing me down a lot (the restore takes around 20 minutes on my ageing laptop). Is there any way I can save a snapshot of the current state of the database, and then quickly restore it?

I'm guaranteed to be the only user on the system, and I have root access. The database dump is ~100MB when tar'ed and gzip'ed. PostgreSQL version is 8.3.

Thanks in advance for any helpful ideas.


texts:
In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already  have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `_Address` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `_Invoice` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  `address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`),
  KEY `address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS
SELECT `a`.`_id` AS `_id`
     FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
     WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS
SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`
    FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
    WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`
  ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `_Invoice`
  ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;


texts:
This is a sample table :

create table test(
    id int,
    json_key json[]
);

insert into test (id,json_key) values
    (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]),
    (2,array['{"key1":"789"}'::json,'{"key2":"456"}'::json]);

json_key output is :

{{"key1":"123"},{"key2":"456"}}

I am trying to execute

select * from test
where json_key->>'key2'='456';

but it does not work :(
how can I run this query?


texts: Oracle to SQL server replication recent data filter options

texts:
The current setup:

A live application DB is on an oracle server, which has a nightly replication over to an SQL server that users (read me) can use to bash out reporting from.

The issue is due to the server's existing on different sites the job is now taking far too long to complete and there is a need to reduce the volume of data being copied to the SQL server daily.

The goal:

To enable archiving to a new SB in SQL server the aim is to replicate a rolling 7 days (ideally) rather than the entire DB and the archiving can all be managed SQL Server side. This allows correction to be made on the Ms side should the archiving ever fail when there is no support available for a day or two.

The issue is most of the tables have no date/time fields, but all of them do have unique ID fields.

I am looking for all manner of ideas the forum can conjur up that would manage this.

Safe things to be assumed: cannot have the live server update the SQL server as part of the transaction, this needs to be done as some form of replication job.

It might but also might not be possible to check values in the SQL server.

One idea I have is to make a log of the last ID number in every table at the start of each replication and store against the date. This would be stored in a table in the SQL server (cannot alter the system DB in any form to put it there.) After 7 days it would become possible to use this table to look up the ID number that you want results >=

This would work for all tables with no date/times as there are no record updates.

There are some record update tables, but these have modify date/time fields and therefore not an issue.

Many thanks in advance for thoughts on this.


texts: How to change all columns from one type to another, changing all FK columns as well?

texts:
In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.

For a few of those, it's an imminent problem, since this optimistic optimization clashes with the fact that we'll (in some cases, very soon) need more rows for these tables than what tinyint allows.

For other tables, the problem lies in the near future, since it has been decided we'll switch providers and DB engine to a mostly compatible RDBMS, but that lacks tinyint support...

Each of these tables has as well between 2 to 6 foreign key relationships pointing to them, so not only we have to change these columns but the FK ones as well.

Could it be possible to somehow script this change in masse, changing all columns where name = id and type = tinyint (e.g. to smallint or even int) and at the same time alter all the tables with FK referencing these columns so they match as well?

I've seen this for changing all columns from one type to another; but I have yet to deal with the foreign-key situation.


texts: Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

texts:
I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "insert" with only one record, the server takes about 0.2 ~ 0.3 secs with MySQL 8. The same "insert" in MySQL 5.7 takes just 0.003 secs or less.

I already discarded Windows Defender or another Antivirus Program, the writing speed of the hard disk is not the problem because I have a Virtual Machine in the same server, the VM has MySQL 5.7 and the result is pretty good too: 0.003 secs or less.

I've tested the same "insert" in different computers with different S.O. and the result is always the same: MySQL 5.7 (or 5.6) takes 0,003 secs or less, meanwhile MySQL 8 takes 0.2 ~ 0.3 secs in every insert with just one record. The "creates" are slower too.

What seems strange to me is that simple "selects" or "selects with joins" are faster in MySQL 8 than previous versions. I think the problem is with the queries that write on disk, but not a limitation with the disk, maybe an assigned limitation in my.ini or in another MySQL settings.

Here I leave you the scripts with the slow performance in MySQL 8:

CREATE TABLE `ciiu_test2` (
`id` INT(11) NULL DEFAULT NULL,
`codbut` VARCHAR(11) NULL DEFAULT NULL,
`ciiu` VARCHAR(8) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

Execution time in MySQL 5.7: 0.016 sec
Execution time in MySQL 8.0.12: 0.140 sec

INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (1, '18237', '2750');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (2, '18238', '9491');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (3, '18245', '9411');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (4, '18248', '2221');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (5, '18264', '3520');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (6, '18265', '4645');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (7, '18268', '6202');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (8, '18276', '6512');
INSERT INTO `ciiu_test2` (`id`, `codbut`, `ciiu`) VALUES (9, '18290', '4210');

Execution time in MySQL 5.7: 0.002 sec
Execution time in MySQL 8.0.12: 0.681 sec

Please, don't recommend me optimize the "inserts" with multiples records. Specifically, I need to get the same times in MySQL 8 with every "insert" that I had once in MySQL 5.7.

I thank you in advance for the help you can give me with this issue.


texts: problem transforming table to First normal form

texts: I have table named Agent. It has attribute named agent_status. It's value can be on or off. I want to conform this table to first normal form. Do i have to create different table for this attribute?


texts: Proper way to store permissions (datatype)

texts:
I'm modeling a MySQL database and it has a users table with the columns:

username
passhash
permissions

The access crontrol will be made on programming level (NodeJS).

Each user may have a different combination of permissions to modules M#.

One user may have permissions to access M1, M2, and M3.
another user may have permissions to access M2 and M4, and so on...

I would like to know what is the best way to store that kind of information.

I read a long time ago about the binary thing like:

000010001 - permitted access to M0 and M4

010000100 - permitted access to M2 and M7

000000011 - permitted access to M0 and M1

I am confused on how to store and retrieve that information. What datatype would be suitable for that purpose?


texts: Convert all the data of table (which have both encoding data) into single encoding either utf8 or latin1

texts:
We have table which consists of two different encoding data (utf8 and latin1) which has been inserted from two different use cases of application. We are getting broken strings issue for other languages text if we get the data in one encoding.We need to convert total table data into single encoding.

Eg: Table X
id name data encoded
1 ébarber - utf8
2 à gogo - latin1

if we use "latin1" connection charset, we get issue with "ébarber"(broken strings). if we use "utf8" connection charset, we get issue with "à gogo"(broken strings).

How can we convert this table data into single encoding either utf8 or latin1?
Please share your thoughts to fix this issue.


texts: Why would SELECT * be magnitudes faster than SELECT foo?

texts:
Consider a table of values and hashes, like so:

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| val        | char(9)  | NO   |     | NULL    |                |
| val_hashed | char(50) | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

The following query finishes in 0.00 seconds:

SELECT * FROM hashes ORDER BY 1 DESC LIMIT 1;

However, this query takes 3 min 17 seconds:

SELECT val FROM hashes ORDER BY 1 DESC LIMIT 1;

I see that while the query is running the process list shows it as status Sorting result. The situation is completely reproducible. Note that there is another process performing INSERT operations on the table continuously.

Why would the more specific query take longer to run than the * query? I've always believed that * queries should be avoided specifically for performance reasons.


texts:
I have just started as a DBA and my first task is to move a 4TB database from a physical server to a virtual server with little down time.

I have found a way to copy the database across, but not how to keep it up to date without having a current full/log backup.

Does any one know of a way I can mirror the database without having to make a full backup and mirror transaction logs?


texts: Contradiction in book related to variable scope in Stored Procedures

texts:
I was reading the 70-461 TSQL training book and the part about stored procedures said this:

  Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1

But in the same book was this example code:

CREATE PROCEDURE Sales.ListCustomerByAddress
(@address AS NVARCHAR(60))
AS
DECLARE @SQLString AS NVARCHAR(4000);
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @address';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @address = @address;
RETURN;
GO

My understanding of the quoted statement is that the @SQLString variable and @address parameter cannot be visible to and referenced by the sp_executesql procedure? A variable is an object that holds a value so If it is not there or cannot be seen (for instance in @statement = ) how can the value be used or passed

Can someone help reconcile this contradiction?


texts: Target server is not communicating(polling) with master SQL Server

texts:
We set up multi-server administration to create a job on the master which are executed on target servers, and we had this issue because the target server was shut down after a virus attack and we set up a new SQL Server with the same name and restore all data including the master database on the new server, all the job is functioning as it used to be but now the issue happened when I am trying to update them, I can update the job at master SQL Server to change the schedule however the target server is not updating since it stopped polling after the original old server shutdown because of the virus attack,
Currently, the new SQL Server Agent is not showing (tsx)
I tried to force poll but it's not working.
my trial to add it as target server is not working with a duplicate error too.
Also, my trial to update the job at the target server is not possible since it's not active to even update
Please advise how can I reconnect this master and target server with out losing all jobs.


texts: Difference between Owned Schemas and Role Membership for Microsoft SQL Server

texts:
Are there differences between Owned Schemas and Role Members?

From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked.

However, the Owned Schemas is not compulsory. It really make me wonder why there is a need to have the Owned Schemas in there.


texts:
I have am having problems backing up my databases after an update.  I have been poking around on my system trying to figure out why.  One query I ran returned this result.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system.  The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists.  Is there an easy way to change the definer to a different account on everything at once?  Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?


texts: MySQL: SELECT and JOIN with not exists values?

texts:
How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this case)

See My SQL Schema:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tar_id` int(11) NOT NULL,
  `cat_idx` int(11) NOT NULL);

CREATE TABLE `time_discounts` (
  `dis_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `tar_id` int(11) NOT NULL,
  `cat_idx` int(11) NOT NULL,
  `discount`double NOT NULL
);

insert into categories values(null, 87, 122);
insert into categories values(null, 82, 120);
insert into categories values(null, 87, 128);
insert into categories values(null, 81, 14);
insert into categories values(null, 87, 135);
insert into categories values(null, 87, 140);

insert into time_discounts values(null, 87, 122, 2.34);
insert into time_discounts values(null, 82, 120, 9.22);
insert into time_discounts values(null, 81, 14, 2.11);
insert into time_discounts values(null, 87, 135, 9.35);
insert into time_discounts values(null, 80, 11, 83.22);

For tar_id = 87 and cat_idx = 122 from categories table we can find values from time_discounts table, but for cat_idx = 140 and 128 values does not exists in time_discounts table. In this case output should be something like this:

| tar_id | cat_idx | discount |
|--------|---------|----------|
|     87 |     122 |     2.34 |
|     87 |     128 |     0    |
|     87 |     135 |     9.35 |
|     87 |     140 |     0    |

My query:

SELECT `c`.`tar_id`, `c`.`cat_idx`, IFNULL(`td`.`discount`, 0)
FROM `categories` AS `c`
LEFT JOIN `time_discounts` AS `td`
    ON `td`.`tar_id` = `c`.`tar_id`
    AND `td`.`cat_idx` = `c`.`cat_idx`;

But i need to filter data by tar_id = 87 and if i add AND c.tar_id = 87 i also gets all values from time_discounts, not only tar_id = 87.

Please help and sorry for my english


texts:
I have a logon trigger that is designed to block certain logins (like 'Login%', or = 'Login1', etc.), if they come from:

certain IP
certain hosts
SSMS

Example code can be found here:
https://paste.ofcode.org/wpJCPFVSEMFwx3iUirwj7X
Problem:
In the trigger code, it also creates report (which host/IP tried to login), and supposed to send this report by email
Database Mail is setup on the instance, and it can send emails just fine
So trigger itself works and does its main function, it blocks logins based on criteria above
But, it does not send emails that it is supposed to do
Also I've tried to grant permission to send emails to Login1 for testing purpose (login that is supposed to be blocked by the trigger), and verified that this login can send emails, but it did not resolve trigger's inability to send emails
How can I make this logon trigger send emails ?


texts: Oracle installation requires old rpm versions, what to do?

texts:
I am installing Oracle 11g R2 on OEL6.

I run the following command, for to check only prerequisites

./runInstaller -silent -responseFile /0/grid/response/crs_install_mk.rsp -executePrereqs

It generated a log that contains the following info:

INFO: Package: libaio-0.3.105: This is a prerequisite condition to test whether the package "libaio-0.3.105" is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: -----------------------------------------------
INFO: Verification Result for Node:testr1n1
INFO: Expected Value:libaio-0.3.105 (i386)
INFO: Actual Value:missing
INFO: Error Message:PRVF-7532 : Package "libaio-0.3.105 (i386)" is missing on node "testr1n1"
INFO: Cause: A required package is either not installed or, if the package is a kernel module, is not loaded on the specified node.
INFO: Action: Ensure that the required package is installed and available.

Whan I check this package:

rpm -qa|grep libaio

libaio-0.3.107-10.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64
libaio-devel-0.3.107-10.el6.i686
libaio-0.3.107-10.el6.i686

And when I try to install

rpm -Uvh libaio-0.3.105-2.i386.rpm

warning: libaio-0.3.105-2.i386.rpm: Header V3 DSA/SHA1 Signature, key ID 73307de6: NOKEY
Preparing...                ########################################### [100%]
        package libaio-0.3.107-10.el6.x86_64 (which is newer than libaio-0.3.105-2.i386) is already installed
        package libaio-0.3.107-10.el6.i686 (which is newer than libaio-0.3.105-2.i386) is already installed

What should I do now? I have newer version than Oracle requires. Ignore it?


texts: Does the replication between an async slave connected to a master withing the cluster affect the performance of the master in the cluster?


texts:
My oracle sever has been upgraded from Oracle 10g to 12c. DBA has created a box for that now my question is how developers will perform testing on that..
    Can anyone provide me the sanity or unit test document or scripts for an oracle upgrade database with respect to developer perspective.


texts: Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g to 11g?


texts: The cost of left out join versus a select

texts:
I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3

I turned on logging for the SQL queries to test some code behavior.

So, assume 2 tables, OneToMany relation. In the code, this will have fields on the objects to represent it.

So for instance, class A will have a list of class B instances as a field.

Suppose I want to get those instances of class B, that are related to class A.

I could simply do return aInstance.bInstances

Or, I could write an Ebean function that asks for the ID of A and gets all the instances from the B table where A is present. The result is the same.

I did both, to see what SQL query Ebean would generate. And it does generate a different one.

The first, asking for the instances related to the object, will get Ebean to generate a statement with a left outer join based on the ID of the instance of A.

The second will do a select on the B table.

I'm wondering about the performance of this. On my test setup, which is fairly small, the left outer join is always faster. But I wonder about production databases that are constantly being updated. I did some googling in to the performance of left outer join and it's pretty much all bad things I've found.

So, what's the hidden cost here? Will left outer join works fast at first but mess things up later? Will the simple select statement prove more reliable, if a bit slower?


texts:
Please bear with me as I ask this question, which looks similar to questions already on DBA@SE.

In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database and Application Server are hosted on this server.
There are two MySql databases - Prod and Staging on the same server. Some of the tables are on MyIasm engine while some on InnoDB.

root     10119     1  0  1233  1012   3 Mar03 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe ...
mysql    10233 10119  9 238047 108896 0 Mar03 ?        12:11:11 /usr/local/mysql/libexec/mysqld ...

Every day a dump of the Prod db is taken and restored in the Staging db. The total size is close to 1 GB and a few tables have more than million rows.

Taking the dump via mySqlDump is fast and takes less than a minute (complete dump - not just deltas from binary logs). Optimizations like turning off auto_commit, foreign_key_checks etc. have been added to the dump.
From a couple of days, the restoration part (to the Staging DB) has become extremely terrible as it takes ~1 hr and paralyzes the application. (The same restore on my dev box takes less than 4 minutes - configurations are different and noone's accessing it)

key_buffer_size is 512Mb [~12.5 % of the total RAM]. table_cache=256 is suboptimal (but there aren't too many requests during that time). read_buffer_size=1M. sort_buffer_size=64M
In the restore script, before inserting data each table is locked, data is inserted and then the table is unlocked.
Slow Query logs are enabled (and most of the queries from the dump find a place there) - Could high I/O be affecting the performance?

I am trying to figure out the root cause so that it may be mitigated. As I have limited access to the server, I am trying to gather possible questions so that I take them to the hosting providers. Could you please provide me with pointers that I should be looking at?

CPU utilization and I/O peak during this time.

Thank you.

[Edit]
- After increasing innodb_buffer_pool_size, the restore time has reduced to an extent.

This is the sample dump, that gets generated:
SET FOREIGN_KEY_CHECKS=0;
SET unique_checks=0;
SET AUTOCOMMIT=0;

// The scripts go here:
DROP TABLE IF EXISTS `access_tokens`;
CREATE TABLE `access_tokens` ( ... );
LOCK TABLES `access_tokens` WRITE;
INSERT INTO `access_tokens` VALUES (...) // All of the data is in a single insert
UNLOCK TABLES;

...
// Script for other tables

SET FOREIGN_KEY_CHECKS=1;
SET unique_checks=1;
COMMIT;
SET AUTOCOMMIT=1;


texts: SQLVDI and Backup Database event viewer errors

texts: We recently started seeing SQLVDI and MSSQLSERVER errors poping up on a number of our machines.  Shortly after event 3197 (I/O is frozen on database x), we start seeing SQLVDI 1 errors (signal abort) and MSSQLSERVER 3041 errors, failure to complete the command BACKUP DATABASE. We run our backups once daily, and looking at our DB backups, they restore fine and are not corrupt.  But batches of these errors are happening throughout the day, with no logs of our maintenance plans attempting to run at those times.  Even stranger, we are seeing these errors occouring on machines that do not have SQL Server 2012 installed.  Any ideas as to what might be causing these errors? or if they can be passed off as nuisance errors?


texts: AD groups and ms SQL security: is this default behavior?

texts:
I have a question regarding security in MS SQL Server (right now, I'm trying this on a SQL 2016).

Scenario:
User AD\testuser is in both AD security groups (AD\testgroup1 and AD\testgroup2).

A new database testDB is created.
A new table Testtable is created in testDB.
Login is created for AD\testgroup1
User AD\testgroup1 is created in database testDB
User AD\testgroup1 have select permission on table testTable.

We log in as AD\testuser, user only has select on TestTable --> as expected

Login is created for AD\testgroup2
User AD\testgroup2 is created in database testDB
User AD\testgroup2 granted for select, insert, update on table testTable.

We log in as testuser, user has select, insert, update on TestTable  --> as expected

Login AD\testgroup2 is removed from server (so AD\testgroup2 is still a use rin the database, but login is removed)

We log in as testuser, user has select, insert, update (we were under the impression this should only be select)

Access for user AD\testgroup2 is revoked in database testDB

We log in as testuser, user has select, insert, update (we were under the impression this should only be select)

User AD\testgroup2 is dropped in database testDB. When we test with same user, user only has select --> as expected

Why do we still get the highest permissions, even when AD group is removed from logins, and disabled in DB users?


texts:
I feel a bit confused when I read about RMAN!
what are the differences between:

RMAN> backup as backupset
RMAN> backup database;
RMAN> backup database plus archivelog;
RMAN> backup as backupset database include current controlfile;

what is the difference between backup database and backup as backupset
also how to backup the database + needed archivelog + controlfile + spfile in one command for a full restore when needed?
is there any site explains this stuff in details with examples?


texts:
I am struggling with query performance.  I'm working with historical records of who has voted (or not) in an election.  I have data for nearly 40 million ballots over 22 election.  When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database.  So I need a system that will let me merge new data into the existing data.

I have a table 'elections' with 22 rows:

CREATE TABLE `elections` (
  `election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `election_type` enum('primary','general','special','board') NOT NULL,
  `whose` enum('state','other') NOT NULL,
  PRIMARY KEY (`election_id`),
  UNIQUE KEY `date` (`date`,`whose`),
  KEY `whose` (`whose`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

And a table 'ballots' which is initially empty:

CREATE TABLE `ballots` (
  `voter_id` int(10) unsigned NOT NULL,
  `election_id` int(10) unsigned NOT NULL,
  `status` enum('unreturned','invalid','valid') NOT NULL,
  PRIMARY KEY (`voter_id`,`election_id`),
  KEY `election_id` (`election_id`),
  KEY `status` (`status`),
  CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My raw data obviously does not contain an election_id.  I need to look up the election_id based on the date.  I load my raw data initially into a 'temp_ballots' table which has ~40M rows:

CREATE TABLE `temp_ballots` (
  `voter_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `status` enum('unreturned','invalid','valid') NOT NULL,
  KEY `date` (`date`),
  KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And now, the part I struggle with.  I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots.  (Then I will drop temp_ballots.)  The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow.  I think I've indexed the appropriate columns.  What am I doing wrong?

INSERT INTO `ballots` (voter_id, election_id, status)
  SELECT voter_id, election_id, status
  FROM elections NATURAL JOIN temp_ballots
  WHERE whose = 'state'
  ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;

I am using MariaDB 10.0.36, so I do not have the ANALYZE statement.  But here is the output from EXPLAIN:

+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
| id   | select_type | table        | type | possible_keys | key   | key_len | ref                   | rows | Extra                 |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
|    1 | SIMPLE      | elections    | ref  | date,whose    | whose | 1       | const                 |   22 | Using index condition |
|    1 | SIMPLE      | temp_ballots | ref  | date          | date  | 3       | voters.elections.date | 1123 |                       |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+


texts: How to refer to individual columns of a record returned by a function?

texts:
I have a function that returns setof record type.

CREATE TYPE CIR_TYPE AS
(
    ID integer,
    path text,
    cycle boolean
);

CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$

DECLARE
    r CIR_TYPE;

BEGIN
    For r in WITH RECURSIVE graph(ID, path, cycle) AS (
                SELECT id AS id
                     , ARRAY[parentid, id] AS path
                     , (parentid = id) AS cycle
                FROM  mytable

                UNION ALL

                SELECT d.id, d.parentid ||path, d.parentid = ANY(path)
                FROM   graph g
                JOIN   mytable d ON d.id = g.path[1]
                WHERE  NOT g.cycle
             )
             SELECT DISTINCT *
             FROM   graph
    Loop

        If r.cycle= TRUE then
        Return NEXT r;
        End if;

    End loop;

END;
$body$ LANGUAGE plpgsql;

When I execute the above function I get an output like below:

circular_ref
----------------------------
(0,"{1,0,1}",t)
(1,"{0,1,0}",t)

Here 0 and 1 are ID, {0,1,0} and {0,1,0} are path and t indicates cycle.

I want to print the output like

ID     |    Path
----       ------
0      |   1,0,1
1      |   0,1,0

Please suggest how to get the expected result.


texts:
Just curious to get an issue sanity checked  . . .

I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server.

Looking at perfmon I can see very high Avg. Disk Write Queue. Looking at SQL   ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance.

The presence of CXPACKET waittypes was high, this I believe is the result of parallelism being enabled and high fragmentation of the underlying indexes couple with out of date statistics.

What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.

Thanks !


texts: What reason is there to create a physical table for a report output?

texts:
I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view or a stored procedure to collect the data. Occasionally I've seen reports use stored proceedures which then use physical tables rather than temporary tables to store and manipulate data sets. In these cases the report output often exists as a table like rpt_ap_vendors or similar, and it may or may not be devoid of data when not in use.

These are always cases where the report is generated on-demand, so this is not a case where a report could be generated once and served multiple times, and there are not multiple reports/stored procedures accessing this data at the same time.

What reason would there be for using physical tables for reports like this? Is there a logical, technical or performance related reason to do so? In generating reports I personally have always used views and stored procedures with temporary tables or better yet derived tables to avoid extra disc reads involving clearing out/deleting a temporary table.


texts: Recalculate row size in SQL Server 2008?

texts:
It is possible to recalculate row size in SQL Server 2008? Take this example:

declare @counter int
declare @statement nvarchar(max)
set @counter=0
drop table kua2
create table kua2(id int)
while @counter<307
begin
    set @statement = N'alter table kua2 add id'+CAST(@counter as nvarchar(max))+N' nvarchar(max)'
    exec (@statement)
    set @statement = N'alter table kua2 drop column id'+CAST(@counter as nvarchar(max))
    exec (@statement)
    set @counter=@counter+1
end

alter index all on kua2 rebuild

dbcc cleantable (0,'kua2',0)

alter table kua2 add id0 int
alter table kua2 add id1 int
alter table kua2 add id2 int
alter table kua2 add id3 int

When I add id3 column, I got warning about 8060 bytes, but the table has only 5 int columns, it still count the dropped nvarchar(max) columns in row size.

The only thing that helps is recreate the table. But for several reasons I don't want to do that. Is there any way to tell the SQL Server to recalculate the row size somehow?


texts:
I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back from my select, what am I doing wrong?

SQL> select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';

ACTIVITY_MESSAGE
--------------------------------------------------------------------------------
REGEXP_REPLACE(ACTIVITY_MESSAGE,'NULL',NEW_VALUE)
--------------------------------------------------------------------------------
Added: null
Added: 1234567891

Added: null
Added: 1234567890 Expires: 12/31/2016

SQL> begin;
 2  select activity_message, regexp_replace(activity_message, 'null', new_value) from organization_activity_log where event = 'ADDED' and activity_item = 36 and activity_message = 'Added: null';
 3  commit;
 4


texts:
I'm trying to set Windows authentication for a postgres server.

The process is not well documented and makes the whole thing, obscure.

I found this doc but it is quite unclear.

In the necessary steps, the doc says that you need to create a domain user. Yet in the screenshot, it says DomainServiceAccount

Once, you've created the domain user and that you set your postgres service with the domain user, you need to create a Service Principle Name to your service account with the following command line in the AD server

setspn -S POSTGRES/[fully.qualified.domain.name] DOMAIN\[service_account_name]

Questions are the following

Is service_account_name = DomaninServiceAccount = Domain user?
If my first question is correct, that I create a user called SuperPostgres, I set the service as mentioned in the doc with the user SuperPostgres, that I create a SPN with the command line spn and set the relevant role as mentionned in the doc, is it certain that Postgres will allow an AD user like AndyKw to connect to the postgres db?


texts: Updating linked server with temp table join

texts:
I am performing the following operation:

Update A set A.col1 = B.col1
From LinkedServer.LinkedDatabase.dbo.remoteTable A
JOIN #localTempTable B on B.column = A.primaryKeyColumn

The performance is terrible and the query takes what seems to be forever,
given that i can't just create a table to store the temp table data in, what can I do in SQL Server 2014 to improve this? I am considering running the query from the linked server instead.


texts:
How can I check with a T-SQL Command what decimal separator is in use in a given user context.

Microsoft Windows Server 2012 has a bug where the regional settings for Swiss German introduced an incorrect decimal separator and digit grouping
KB3060383

We are running our batch jobs via Sql agent with service accounts and we noticed that some of these service accounts are using the wrong decimal separator, some not (since some steps fail). So we want to have a step in each job that displays what settings it is using when running. Checking the regional setting alone doesn't help due to the above mentioned bug. The Windows servers have been patched but we still notice these problems.

I'm aware that we could code the jobs more defensive however that would cover the wrong settings and would make it even more difficult to discover where the bug is still active.


texts: Microsoft Access 2003 vs SQL Server 2005 Express

texts: There is an Access 2003 database that each "corps" has to manage themselves containing personal, training and material data. It's written very poorly. Besides that, the server only runs Windows 2000, so SQL Server 2005 Express is the highest that it can run. What advantages would I see upgrading it to SQL Server? Speed, reliability and ease of upgrading would be a plus. If it can handle multiple users at the same plus, that'd be the best Plus right now


texts: Importing large SQL dump with millions of INSERT statements

texts:
I need to import a big .sql file (8.1GB when unpacked) into PostgreSQL. I tried to use \i /path/to/file.sql but it is way too slow.

How does one speed up the import? I need to import this data weekly.

The first 2000 lines can be found here, while the compressed 1 GB dump can be found here

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.3
-- Dumped by pg_dump version 9.5.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: rpo; Type: SCHEMA; Schema: -; Owner: -
--

That's the only way I can get the data. The full file has approx. 38,000,000 lines. How can I speed up the import?


texts: How to clear all plans from a single database?

texts: I want to clear out the procedure cache for just one database, not the entire SQL Server. Is there a way?


texts: I have a huge table in my mysql 5.6.19 database -around 5.7 gb of data- and there is a need to alter a description column which is currently set to text, to be able to contain around 500000 chars. Now my question is what is the right way to preform such a proccess. What will be the effect of the disk space when doing it? Can indexes be possibly harmed after this operation? Any help would be highly appriciated here. Thank you very much


texts: Mysql: Schema/Query Performance Approach for aggregated mailbox folders

texts:
I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes:

a) with all messages they receive
 b) all messages they receive filtered by users they are friends with

For all mailboxes the total/unread numbers inside shall be displayed.

This leads me to the need of the following tables:

 - users (id, username,...)
 - friends (user_id, friend_id)
 - messages (id, sender_id, receiver_id, read_status, mailbox_id, ...)
 - mailbox (id, name, owner_id, ...)

My question regarding the perfomance (thinking of a situation where there are many messages and users):

Is is better to calculate the total/unread values for each mailbox for each page view?

Select count(.) as total,
.... ,
Group By mailbox_id, friend_status, read_status

or

Is it better to store these information in the table mailbox in extra fields like total_msg, total_unread, total_friends, total_friends_unread - and update these values upon each event (new message, read message, new friends, friends break up, etc...)?

Would there be a 3rd approach to be considered?

Thanks for help!


texts:
I would like to configure PostgreSQL 9.4 to allow connections from database users with passwords from PHP on both the command line and through Apache on a CentOS 6.6 box.  I installed PostgreSQL with yum and the only configuration changes I have made are in the pg_hba.conf file:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

I have a test database called test and a user named psql with the password of psql. Logging in on the command line works fine:

psql -U psql -W test
Password for user psql:
psql (9.4.5) Type "help" for help.

test=>

The privileges are:

My PHP code is:

<?php
$user = 'psql';
$pass = 'psql';
try {
    $dbh = new PDO('pgsql:host=127.0.0.1;dbname=test', $user, $pass);
    foreach($dbh->query('SELECT * from test') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . PHP_EOL;
    die();
}

Yields the error:

  Error!: SQLSTATE[08006] [7] FATAL:  Ident authentication failed for user "psql"

I tried using trust as well, but that didn't work either, and I would like to know how to allow access for database users with passwords.  I would like to avoid using ident.

Could you tell me what's wrong?


texts: Unable to retrieve query plan of running query because of blocking

texts:
I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes.  Probably some plan caching issue.  But the problem is that I am unable to retrieve the query plan because of blocking in tempdb while the query is running.  I am using the below dmv's to try to retrieve the query plan

FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'sp_name'
ORDER BY statement_start_offset

The above query to retrieve the plan gets blocked by the long running  problem query because the above query needs a schema S lock on an object on tempdb.  They long running query queries and updates a temp table and has option recompile on it.

Has anyone come across this issue?


texts: Adding where clause in LEFT OUTER JOIN giving unexpected result

texts:
SELECT abc_discussions.id as id,
       abc_discussions.subject as subject,
       abc_todo_lists.title as todo_list_title,
       abc_todos.title as todo_title,
       abc_files.title as file_title,
       count(abc_comments.comment) as no_of_comments,
       MAX(abc_comments.posted) as last_comment_posted
FROM abc_discussions
LEFT OUTER JOIN abc_todo_lists
ON abc_discussions.commenttable_type = 'todo_list' AND abc_discussions.commenttable_id = abc_todo_lists.id
LEFT OUTER JOIN abc_todos
ON abc_discussions.commenttable_type = 'todo_item' AND abc_discussions.commenttable_id = abc_todos.id
LEFT OUTER JOIN abc_files
ON abc_discussions.commenttable_type = 'file' AND abc_discussions.commenttable_id = abc_files.id
LEFT OUTER JOIN abc_comments
ON abc_discussions.id = abc_comments.discussion_id
GROUP BY abc_discussions.id

gives everything correctly, but if I will add  WHERE abc_discussions.project_id = 2 it gives todo_list_titleandfile_titleas null, but if I will addWHERE abc_discussions.project_id = 1` it gives again correct result  what may be the cause ?

abc_discussions table

CREATE TABLE $abc_discussions_table(
            id bigint(20) NOT NULL AUTO_INCREMENT,
            project_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL,
            subject VARCHAR (2000) NOT NULL,
            description VARCHAR (20000) NOT NULL,
            posted datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            commenttable_type VARCHAR (200),
            commenttable_id bigint(20),
            UNIQUE  KEY id(id)


texts: How to create stored procedure that returns query result?

texts:
I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There are situations when I need to pull whole table result set and sometimes I need to look for specific record only (filtered by id). Here is example of the query:

SELECT
    TRIM(t1.rec_id) AS rec_id,
    TRIM(t1.date) AS date,
    TRIM(t1.name) AS name,
    TRIM(t1.address) AS address,
    TRIM(t2.year) AS year,
    CASE
        WHEN LENGTH(TRIM(t3.percent)) <> 0 THEN CONCAT( ROUND( TRIM(t3.percent) * 100, 2) ,'% Whole' )
        WHEN LENGTH(TRIM(t3.partial)) <> 0 THEN CONCAT( ROUND( TRIM(t3.partial) * 100, 2) ,'% Partial' )
    END AS percent
FROM table1 t1
    LEFT OUTER JOIN table2 t2
        ON TRIM(t1.rec_id) = TRIM(t2.rec_id)
    LEFT OUTER JOIN table3 t3
        ON TRIM(t1.rec_id) = TRIM(t3.rec_id)

-- Here I would like to add WHERE clause filter
-- If RecID argument is not NULL and is valid then query should filter
-- and return only one record. If not return all records.

ORDER BY t1.rec_id

Query above usually returns around 500-600 records. It's the main source of my application. In some situations I need all records and sometimes only specific record. So in my comment inside of the code above I explained the situation. The stored procedure should have one argument RecID. If that argument is NULL then stored procedure should return all records and columns. If ti's not NULL then it should look for specific RecID and return that row with all columns. I have done some work in SQL 2008+ where I used stored procedures. I'm wondering if someone can show me an example on how this would work in Oracle? I use Oracle 11g with ColdFusion 10. If someone have good example on how this can be completed please let me know. Thank you!


texts: Multiple THEN outputs on a PostgresQL query

texts:
Helllo there,
I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis.

As a such, I'd like to use a CASE expression with an input condition and two assignments if the condition is satisfied.

Something like

SELECT
    CASE
        WHEN t."page" ~* '...' THEN t.type = 'Product', t.something = 'something else here'
END AS "page_type"

Reading through the documentation, but also checking for several examples I couldn't see this doable. Am I missing something?

Assuming is not, the only thing I can think of is using a function that returns an array or something similar.
Would that be an a possible approach? If so, any example I can be pointed out to learn a decent DDL pattern?

Thanks

UPDATE To support @a_horse_with_no_name answer

SELECT *
FROM "V_2019-06-10_to_2019-06-20_GSC" t,
            (
                SELECT
                    Regexp_replace(t."page", '...', '\1') as node
                    FROM "V_2019-06-10_to_2019-06-20_GSC" i
                    WHERE i.page = t.page
            ) AS "IFs"

The above generates an error in referencing the t table.
If I go for a CROSS JOIN LATERAL, the query works, but then the GROUP BY I have at the end of the outer query cannot dedup anymore the results

UPDATE 2

Here's a simplification of the query. It's of course much more complex than this, but hopefully it suffice.

SELECT -- t."page",
    "URL".node, t.area, "URL".page_type,
    CASE
        WHEN "URL".page_type = 'ABC' THEN <here I need my value>
        ELSE ''
    END AS "Node_Type",
    FROM "V_2019-06-10_to_2019-06-20_GSC" t
    CROSS JOIN LATERAL (
        SELECT
            Regexp_replace(t."page", '...', '\1\2\3\4\5\6') as node,
            CASE
                WHEN t."page" ~* '...' THEN 'ABC'
                WHEN t."page" ~* 'filter' THEN 'DEF'
                ELSE 'Other'
            END AS "page_type"
    ) AS "URL"
    GROUP BY "URL".node, t.area, "URL".page_type
;

A URL processed by this query could look like this /level1/level2/level3/?filter=123

Idealy I'd like to:

capture level1/level2/level3 with the Regexp_replace, and I have the
regexpr working for this.
I need to identify once again whether the filter is included in the page to populate the page_type, and the case above should be ok.
capturing 123 as part of the outer CASE "Node_Type", but I cannot get this done, hence my first thought of a CASE processing two
outputs.

Is there an effective way to get this out?


texts: Using Oracle client to connect to remote database

texts:
Although I can do a telnet to remote oracle port I am unable to connect.
I have a following tnsname.ora connecting to oracle database using sql plus:

remoteOracle =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.66.1.61)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

My sqlnet.ora

# sqlnet.ora Network Configuration File: C:\ORACLE\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

When I try to connect using this I always get following error

ORA-12504: TNS listener was not given service_name in connect data.

And toad shows following error:

ORA-12170 TNS connection Time out


texts:
I have been tasked to perform backups of a Sybase SQL Server 12 database, but was just given the user and password. I tried logging into the server where it runs as a domain admin and ran Sybase SQL Anywhere 12 and clicked on Connect to a database. I selected connect to a running database on this computer. I selected Database Authentication and entered the supplied user and password. I entered the hostename as the server and what I assume the database name to be, but will not connect. I get the following error message:
Connection failed.
[Sybase][ODBC Driver][SQL Anywhere]Database server not found
Fri Jul 10 2020 10:39:41
10:39:41 Attempting to connect using:
UID=DBName;PWD=****;DBN=DBName;ServerName=MyHostname;CON=SQL_DBC_20a7e6f0;LOG=C:\Users\MyUser\AppData\Local\Temp\4\testcon17105042707363.tmp;ASTART=NO
10:39:41 Attempting to connect to a running server...
10:39:41 Attempting SharedMemory connection (no sasrv.ini cached address)
10:39:41 No named shared memory buffer found
10:39:41 Not attempting to autostart server
10:39:41 Cannot connect to server


texts:
I'm trying to set up a xtraDB Cluster consisting of 3 nodes. Now i've got the cluster up and running and per the instructions i'm trying to set up ProxySQL for load balancing.

So i've installed proxySQL on all 3 nodes. And now i'm trying to configure it with the admin tool. But everytime that i run the command:

[root@node1 log]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable

It comes back with:

This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)

ProxySQL read/write configuration mode is singlewrite
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql_admin'@'' (using password: YES)
Please check the ProxySQL connection parameters! Terminating.

Now i would say the error is pretty straight forward. However i have the user proxysql_admin properly defined in both the MySQL cluster and in the .cnf files.

Here's my proxysql-admin.cnf file:

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME='proxysql_admin'
export PROXYSQL_PASSWORD='placeholder_pass'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'

# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='proxysql_admin'
export CLUSTER_PASSWORD='placeholder_pass'
#export CLUSTER_HOSTNAME='localhost'
export CLUSTER_HOSTNAME='ccloud'
export CLUSTER_PORT='3306'

# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='placeholder_pass'

# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='placeholder_pass'

# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID='10'
export READ_HOSTGROUP_ID='11'

# ProxySQL read/write configuration mode.
export MODE="singlewrite"

# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf

Here's the user on the mysql cluster:

mysql> select User,Host from mysql.user;
+----------------+-----------+
| User           | Host      |
+----------------+-----------+
| proxysql_admin |           |
| proxysql_admin | %         |
| mysql.session  | localhost |
| mysql.sys      | localhost |
| proxysql_admin | localhost |
| proxysql_user  | localhost |
| root           | localhost |
| sstuser        | localhost |
+----------------+-----------+

I've tried changing up the interfaces a few times but that doesn't seem to be the problem.
Anyone have any idea why my user isn't connecting?

Just in case, here's the my.cnf of the cluster on the node i'm trying this on:

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
server-id=1
datadir=/mysql-data
socket=/mysql-data/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name=ccloud
wsrep_cluster_address=gcomm://ip1,ip2,ip3

wsrep_node_name=pxc1
wsrep_node_address=ip1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:placeholder_pass

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2


texts: Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars

texts:
Trying to use REGEXP_REPLACE to clean up a text field in a table.
Here's my query:
select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean
from tmp_table2
WHERE txn_title like '%?%'

Here are my rows:
original                                              clean
�� ���� ?���?� ����� Lionel Casson ��    Lionel Casson
��  �?� ?���?� ?���� �?� �. �?����
� Les phobies � l'enfant : impasse ou passage ?      Les phobies chez l enfant impasse ou passage
- Snälla du! Kan du sätta? : om vägledni i förskolan   Sn lla du Kan du s tta dig om v gledning i f rskolan

The first three rows above are ok for my needs, but the 4th row is not, I want to keep those non-latin characters (words with umlauts, etc...)
How can I keep those character types (non-latin) by using one regex in the replace function above, while keeping the rest of the results as shown in the 'clean' column?


texts: Warning: #1292 Incorrect datetime error when using DATE_ADD()

texts:
Doing this works fine:
SELECT * FROM table WHERE foo < '2021-01-09 00:00:00'
But this one:
SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00'
results in
Warning: #1292 Incorrect datetime value: 'foo'
What could possibly be causing this?
(MariaDB 10.4.17)


texts:
Suppose I have three tables: meta_tags, users, posts. (just an example)

Users and Posts both could have tags, so my meta_tags table could be structured like

 meta_tags
   - user_id
   - post_id
   - tag

But, I want my meta_tags table to be extendable. If I later create an item table that could also have a tag, I'd need to adjust my meta_tag table with a new _id column.

Would it be better to instead format my meta_tags table like

 meta_tags
   - thing_id
   - thing_type
   - tag

so that my query would be

 SELECT tag FROM meta_tags WHERE thing_id = 1 AND thing_type = "user";

vs

 SELECT tag FROM meta_tags WHERE user_id = 1;

or, maybe I should have separate tables? user_tags, post_tags, etc..

I guess my question is...which way is considered best practice? Are their significant performance impacts I should be aware of?


texts: In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?

texts:
Given a Table with a column col1 like this:

create table MyTest (
    col1 int NULL,
    col2 decimal(7, 2) NULL
);

insert into MyTest values ( 1, 1.1);

Is there a way for a trigger for update to decide which of the following update statements was executed ?

A:

update MyTest set col1 = 3;

B:

update MyTest set col1 = 3, col2 = 1.1;


texts: Microsoft SQL Server -> Postgres Data Migration

texts:
I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is in the cloud.

What is the best way to manage this ETL process? My current approach is to just transfer over the tables we need (about 30) and then re-initialize the materialized views. I am willing to pay for a migration tool if it can fully automate the process, otherwise I'll go with the simplest solution our bandwidth can support.

Thanks!


texts:
I created a database in MySQL Workbench using Ubuntu.

Is there a quick way to get an EER diagram of that database?


texts: Duplicate key error for composite primary key when running update query without having duplicate

texts:
I have a table with the below schema

    CREATE TABLE `my_field_table` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' ,
  `bundle` varchar(128) NOT NULL DEFAULT '',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `entity_id` int(10) unsigned NOT NULL,
  `language` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 49 (my_field)'

and the following two entries already present in the table.

+-------------------+----------+---------+-----------+----------+
|    entity_type    |  bundle  | deleted | entity_id | language |
+-------------------+----------+---------+-----------+----------+
| node              | article  |  0      | 9001      | 5555     |
| node              | article  |  0      | 9001      | abcc     |
+-------------------+----------+---------+-----------+----------+

When I run the following update query :

UPDATE `my_field_table` SET language='9002' WHERE entity_id = 9001;

I get the error message Error Code: 1062. Duplicate entry 'node-9001-0-9002' for key 'PRIMARY'

However I do not see such a combination of the field values already present which confuses me on the duplicate entry error. Usually I have at least one auto-increment key which I have as the primary key but this table does not have one and my lack of experience with DB isn't helping me either.

I tried searching for similar questions but the solutions suggested is to either check for existing entry or to create a new table with same structure and simulate this to check if my table is corrupted. The former does not fetch any results while I am able to reproduce the same error with the latter.

Some insight into the cause of this error would be greatly appreciated.


texts: Managing ibtmp1 File Growth on MySQL 5.7

texts:
We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where we thought we might fill up disks and whatnot.

From the (slightly vague) docs on the subject, it looks like the file can only grow in size, and the only way to reduce it is to restart the server (I take note of the comment about bad queries using up lots of temp space being the cause of the problem).

Is there actually any way to manage this file, whilst keeping the server online? I know disk space isn't such a limiting factor these days, but in the cloudy world we now live in, it costs money to keep extending disks without ever being able to shrink them again.


texts: How should we give privileges/permissions to group to access different db's

texts:
I'm new to working on RDS postgresql instance, I've a group and added a couple of users to the group. I need to give permission to users in the group to allow them to access other dbs like x ,y and z

I logged in as admin user to y database and gave permission to the group, but the user is not able to view tables in other dbs. Group name is dev_role. All tables are in public schema.

y=> ALTER DEFAULT PRIVILEGES GRANT SELECT, INSERT, UPDATE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dev_role;
ALTER DEFAULT PRIVILEGES

Even Tried

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

The user logged into y db and was trying to run \dt, the user is getting an error:

Did not find any relations.

  psql (10.11, server 11.5)

What could be the issue and can someone point me correct way to give permissions.


texts:
I am working on a project that does real-time monitoring of data being transmitted, and logs if there are any of the various type of errors, etc. The CRUD operations are real-time. We initially planned on using PostgreSQL, but the problem we are facing is that PostgreSQL is not fast enough to handle real-time CRUD operations even after tweaking it a little; same goes for MySQL and other biggies. SQLite performs a lot faster than them, but is soon almost dead when the database size reaches a few hundred MBs. Another constraint is that the monitoring is to be done over network.

Is there any database that can handle such fast operations? Or should I opt for a NoSQL database?

EDIT (Regarding design):

The design is normalized to the maximum extent it was possible. The stored data is almost mutually independent, so there are very few joins. Also, I said "a few hundred MBs" just as a reference. The actual databases that we work upon are multiple GBs in size. Lots and lots of data is inserted every second and retrieved also.

Talking about PostgreSQL, it takes 5-7 times the time taken by SQLite in the tests that I ran on my data.

EDIT (Regarding speed):

I'll like to mention a worst-case scenario that can happen.

Suppose that the main application is being used at 10 instances (or PCs). They all interact with a cental DBMS and insert data into it. Now, every single app will have many threads doing some operations on the data that is being transmitted in real-time. The app reports if there is erraneous data in the streaming or not. And since the data is analyzed at packet level, lots of errors can happen in a sec. Based on some very basic calculations, the worst case may require an insertion rate of ~3k rows per second per instance, with each row having some 8-10 associated columns. I tested such a test on my machine(4GB ram, QuadCore), and SQLite is able to do this in ~1 sec over network. I had tweaked PostgreSQL a little, and it did that same in ~5 sec (I'd admit that I did not optimize it a lot, as I am not a pro in the DBMS field). But the bottleneck comes as the db size grows bigger in SQLite; the insertions seemingly go on almost fine, but the reads take a lot of time. I had tested it with a DB size of 3gb myself.

Our major concern is the insertions, which, in worst case will be ~3k per app instance and in average case ~500-1k insertions per instance.


texts:
I am well aware of what those 2 do. $in take .3 seconds. And $all take 26 seconds. Huge different. If anything $in should take longer because it returns more data.

Also if the limit is removed, $in actually is much faster.

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200);

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200);

This is result

/* 88 */
{
  "ts" : ISODate("2012-09-11T06:57:26.801Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$all" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 48,
  "nreturned" : 48,
  "responseLength" : 60002,
  "millis" : 3821,
  "client" : "127.0.0.1",
  "user" : ""
}

/* 89 */
{
  "ts" : ISODate("2012-09-11T06:57:43.147Z"),
  "op" : "query",
  "ns" : "newisikota.tablebusiness",
  "query" : {
    "LongitudeLatitude" : {
      "$nearSphere" : [106.772835, -6.186753],
      "$maxDistance" : 0.053980478460939611
    },
    "Prominent" : {
      "$gte" : 15.0
    },
    "indexContents" : {
      "$in" : [/^soto/, /^nasi/]
    }
  },
  "ntoreturn" : 200,
  "nscanned" : 200,
  "nreturned" : 200,
  "responseLength" : 249598,
  "millis" : 320,
  "client" : "127.0.0.1",
  "user" : ""
}

Note: the $all query can run for 26 seconds once in a while.

The explain result is the following:

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^soto/, /^nasi/] } }).limit(200).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 48,
        **"nscannedObjects" : 48,**
        "n" : 48,
        "millis" : 8563,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}
>

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$in" : [/^soto/, /^nasi/] } }).limit(200).explain();
{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 200,
        **"nscannedObjects" : 200,**
        "n" : 200,
        "millis" : 516,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
        }
}

Notice that the $in search scan more object.


texts: I get a query error 'unknown column' while the column exists

texts:
I get error (1054): Unknown column 'wp_jpnx_survey_code.supplier_id' in 'field list'But that column does exist... Does anyone know why or how to fix this?

This is the code I used:

CREATE VIEW survey_codes_complete_overview AS SELECT *,
wp_jpnx_survey_code.supplier_id AS a_id,
wp_jpnx_survey_supplier_info.supplier_id AS b_id FROM
wp_jpnx_survey_code AS a LEFT JOIN wp_jpnx_survey_supplier_info AS b
ON a.supplier_id = b.supplier_id;


texts: How should I handle Products with different attributes in my database structure?

texts:
We have an e-commerce site (web application) built with PHP.

There are different types of products like mobiles, tshirts, watches, books.

A mobile can have attributes like, for example, ram, model name, os.
A t-shirt can have attributes like, for instance, color, size.

My questions are the following:

Is it better to save all attributes in a single table called Products (even if there will be/it will create a lot of 'NULL' marks)?
Save different types of products in different tables like Mobiles, Tshirts, etc.?

In the second case, more tables will be created, see the two images below:

As suggested via comments, I am using the EAV model, so I drew the following design [ image 3 ] :

as suggested in answer, instead of eav , i combined all 3 tables in one table as   product_information + attributes_list + attributes_values = products information i drew all in one table  [image 4 ]:

Please suggest me is i need to go with image 3 or image 4 ?


texts: How to migrate a database from a local to a remote server?

texts:
I have a postgres db that I have created and need to deploy remotely. From reading online I have understood that to do that I need to modify the following files :

pg_hba.conf
postgresql.conf

From what I read I need to modify them and somehow add them to my database?

My question is:

How do I modify the 2 files to deploy a database online? I read the documentation but its too complicated for my understanding.(ie what is needed and why?)
How do I add them to my database online? My idea was to create a database dump of the local DB, then push the dump on a new database on the remote computer, but how do these 2 files fit in there? Where should there be placed? I cannot find details on this.

note. I have never done distributed programming before, my question might be naive but I really have no idea how to proceed.

I am using osX operating system,but will be using linux on the remote computer.


texts: How to apply a newly-created Domain to an existing Column?

texts:
In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other.

CREATE DOMAIN fuel_domain_
  AS character varying
  COLLATE pg_catalog."default"
  NOT NULL
  CONSTRAINT fuel_check_ CHECK (VALUE::text = ANY (ARRAY['coal'::character varying, 'gas'::character varying, 'hydro'::character varying, 'wind'::character varying, 'other'::character varying]::text[]));
ALTER DOMAIN fuel_domain_
  OWNER TO postgres_admin_;

  ➤ How do I apply this new domain to an existing column?

The doc seems to say that a domain should be specified in place of the usual data type when declaring a column.

So I tried and failed to re-define the column’s data type from VARCHAR to the new domain. The base data type of the domain is the same, so the data types should not be in conflict. This SQL for a table unit_ and column fuel_ fails with a syntax error at or near "COLUMN":

ALTER COLUMN unit_.fuel_ SET DATA TYPE fuel_domain_;

WORKAROUNDAs a workaround, I was able to delete the column and successfully recreate it with this SQL:

ALTER TABLE unit_ ADD COLUMN fuel_ fuel_domain_;


texts: "Unable to display page" with Oracle 12c Enterprise Manager Database Express

texts:
i'm using redhat 6, installed 12.1.0.2 of oracle and created a database.

database (dev12ee1) is a non-container database, there is also an 11g database on the server (dev11ee1)

lsnrctl status shows standard port of 1521 and 5500

lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JUN-2015 11:46:54

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                02-JUN-2015 12:28:48
Uptime                    14 days 23 hr. 18 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app12/oracle12/diag/tnslsnr/csya-oradev5/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=csya-oradev5.xxxx-dmz.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=csya-oradev5.xxxx-dmz.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app12/oracle12/admin/dev12ee1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dev11ee1.csya-oradev5" has 1 instance(s).
  Instance "dev11ee1", status READY, has 1 handler(s) for this service...
Service "dev12ee1.csya-oradev5" has 1 instance(s).
  Instance "dev12ee1", status READY, has 1 handler(s) for this service...
The command completed successfully

the port settings in sqlplus look like this

SQL> select dbms_xdb_config.gethttpsport() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500

SQL>

the local_listener and dispatchers database parameters are both blank (which should be ok as everything is standard and default).

but when i go to the URL

https://csya-oradev5.xxxx-dmz.local:5500/em/

i get "This webpage is not available"


texts:
I'm trying to change collation on all our databases, and doing it on one of the databases (~15gb) took me 42 minutes in testing even though I put it on SSD disc. Do anyone know any way to speed it up? I don't want to have the production databases offline for hours unless I need to.
I googled and searched around here without finding anything related to speeding up changing collation, I know its a one time thing that most companies probably do before their databases become big enough that its a performance problem, but still if someone have experience with speeding it up I'm very interested.

I'm using Redgate SQL Compare to generate statements that look like this:

ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Summary] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NOT NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Category] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Contact] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [RRule] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL

Thanks!


texts: Every person has a contact info table and every contact has an address

texts:
so i'm still in the design phase of this database. Anyways, every person, whose PK is their name, has a FK to a contact table then I'm very confused as to what's next as every contact table contains a reference to the address table. Person -> Contact -> address
Tables contain as follows:
Person:
PK | name
contact info references contact table (?)

contact:
PK | address(?)
email

Address:
PK | street name (?)

First Question: What would be the PK for my contact table?
Second question: What would be the PK for my address table? Would it even have a PK or is it just referenced?
Essentially one person would have one entry into the contact table, then in the contact table it references the address table and I'm just trying to figure out the best way to do that.