texts: Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

texts:
Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column because the NEW.*/OLD.* format can't be used to compare json columns.
CREATE OR REPLACE FUNCTION on_insert_update_modified()
RETURNS TRIGGER AS $$
BEGIN
  IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
    NEW.modified_at = now();
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$ language 'plpgsql';

CREATE TRIGGER mytable_insert_update
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE on_insert_update_modified();

When I insert into the table which contains json and jsonb columns, I get the following error:
ERROR:  operator does not exist: json = json
LINE 1: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
CONTEXT:  PL/pgSQL function on_insert_update_modified() line 3 at IF

Is there a better way out of this?


texts: Should I add my index after the table is populated or before (MySQL. Aurora)

texts:
I know this has been asked in the past, but sometimes things change over time and I wanted to double check.

I have a table with about 9 Billion rows. Should I add the index before inserting the data or after. I'm using Aurora. Does it matter if I'm adding more than one index?

Everything, I'm aware of says you should do this after the insert, but one of my colleagues is insisting it's faster to do it on the insert.


texts: Stored procedure to validate xml content against dtd

texts:
I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file?

Thank you in advance!


texts: How to convert Postgres from 32 bit to 64 bit

texts:
I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that.

With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of them run in 32 bit or  64 bit).

Until now, I haven't found how to change from a 32 bit install to 64 bit. How can this be done?


texts: mysqldbcompare object definitions differ

texts:
I am using a Galera cluster to replicate MySQL 5.7 databases on Ubuntu 16.04 Server. I am using mysqldbcompare to verify the consistency of the replication. I think my data is consistent because the Data Checks and Row Count checks pass.

I think "Object definitions differ..." means that there is something different about the CREATE statements. My output shows the Auto Increment fields having some differences.

My question is: Will the inconsistencies in the object definitions affect the consistency of my replicated databases as a whole (even though data check passes)?

I would love to know what causes the inconsistencies and how to fix them.

# Checking databases study1 on server1 and study1 on server2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     admin_log                               pass    pass    -
#           - Compare table checksum                                pass
# TABLE     clists                                  pass    pass    -
#           - Compare table checksum                                pass
# TABLE     data1                                   FAIL    pass    -
#           - Compare table checksum                                pass
#
# Object definitions differ. (--changes-for=server1)
#

--- `study1`.`data1`
+++ `study1`.`data1`
@@ -236,4 +236,4 @@
   `sys_pagetime_29` int(11) DEFAULT NULL,
   `sys_pagetime_30` int(11) DEFAULT NULL,
   PRIMARY KEY (`sys_RespNum`)
-) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC


texts: Oracle 11g R2 - same query sometimes fast, sometimes slow

texts:
I have a simple update query on a table with about 100 rows in it:

UPDATE RESULTS_DATA
     SET IMAGE_BLOB=:myBlob
WHERE MEASUREMENT_INDEX=:mIndex
  AND BATCH_ID=:batchId
  AND SYSTEM_ID=:sysId

There are indexes on all 3 columns I am searching on. The BLOB is 1.2MB in size. Sometimes the query takes 0.5 seconds to complete, but other times it takes around 3-4 seconds. Looking in the V$SQL table shows that almost everything is the same between multiple runs of the same query, but the following fields are very different:

IO_INTERCONNECT_BYTES (1277952 on fast query, 2818048 on slow query)
    PHYSICAL_READ_REQUESTS (1 on fast query, 27 on slow query)
    PHYSICAL_READ_BYTES (8192 on fast query, 393216 on slow query)
    PHYSICAL_WRITE_REQUESTS (155 on fast query, 164 on slow query)
    PHYSICAL_WRITE_BYTES (1269760 on fast query, 2424832 on slow query)

This is from running exactly the same query sending exactly the same BLOB. Could anyone tell me why this would happen?

Thanks!


texts: Does Log File contain both committed and uncommitted transactions?

texts:
My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean that Log file contains only committed transactions?

Some sites also state that when COMMIT happens, then dirty page is moved from BUFFER CACHE into LOG CACHE first and afterwards into log file. So I do not understand what happens when COMMIT occurs. I will appreciate any help.


texts: Implicit conversion: when does it hurt performance a lot?

texts:
I know that when data types are the same it is good and it hurts performance when they are not.
But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times it will have a huge impact and performance gets a lot better.
Of course there are many scenarios, e.g. a insert select where the data type of the select is not the same, but compatible, with the insert column. Or it could be on the join predicates.
Where does implicit conversion hurt the most?
Also it seems that one also gets an implicit conversion when the lengths don't match, e.g. joining varchar(10) with varchar(20). How big a deal is these scenarios when we are only dealing with the length of the columns and not their type?
Basically I would like to know when I should worry about the compute scalar operator in the execution plan. How can I know if removing it will have a significant impact?


texts: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

texts:
MySQL version: 5.5.13

The query that I'm using:

SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY  ',';
COMMIT;

Normally it takes 3s to complete. But sometime, the insert query takes about 3-7 minutes to run:

     Id: 97
   User: tom
   Host: 192.168.6.31:27059
     db: test
Command: Query
   Time: 155
  State: logging slow query
   Info: COMMIT

and my database is locked completely (very slow when connecting). The process Id 97 continues running after I try to kill it. The init script shows the [ FAILED ] flag when trying to restart MySQL, so I must use kill -9 and start again.

I will try to disable the unique_checks follow this guide. But I'm trying to find out why it's locked.

There are some Waiting for table metadata lock states on temporary tables:

     Id: 180098
   User: jerry
   Host: 192.168.6.31:54909
     db: test
Command: Query
   Time: 142
  State: Waiting for table metadata lock
   Info: DROP TABLE IF EXISTS norep_locationtmp

and a Waiting for table level lock state:

     Id: 180233
   User: bob
   Host: 192.168.6.31:43537
     db: test
Command: Query
   Time: 65
  State: Waiting for table level lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)
AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,A.`uid` =  NAME_CONST('userid',388)))

There isn't anything related to the above table - v3_zone_date and no deadlock detected in SHOW ENGINE INNODB STATUS. Where should I take a look at first to troubleshoot this case? Let me know if you need any further information.

Reply to @RolandoMySQLDBA:

  The scary part about the query is the self-reference

  You have selfserving_users acting in a self serving manner against
  itself.

I will ask the developer to rewrite it.

  You are hammering the InnoDB Buffer
  Some memory swapping may be going on

I have 40GB RAM and:

innodb_buffer_pool_size     = 20G
innodb_buffer_pool_instances    = 8

The memory graph exhibit that there is no increase.

  Possible full table locking issues that are affecting data pages
  outside the v3_zone_date table (such was with the selfserving_users
  table)

Do you have any ideas to avoid the full table locking issue?

  There may be a way to throttle the LOAD DATA INFILE process on an
  InnoDB table. I cannot give you a solid answer on this one, but try
  this link from Baron Schwartz.

I will trying the fifo method and let you know the result.

UPDATE: Wed Feb 22 12:19:03 ICT 2012

Below is snippet of the SHOW ENGINE INNODB STATUS output when hanging:

---TRANSACTION 1EF3CC26, ACTIVE (PREPARED) 332 sec
68 lock struct(s), heap size 14776, 6933 row lock(s), undo log entries 3465
MySQL thread id 4088, query id 11411947 192.168.6.31 bob
COMMIT
Trx read view will not see trx with id >= 1EF3CC27, sees < 1EF06C8E

Based on the thread id, I found out the culprit is a .NET process which is loading data into the database by following steps:

LOCK TABLES;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA;
COMMIT;
UNLOCK TABLES;
SET unique_checks=1;
SET foreign_key_checks=1;

It seems that the insert processing hangs at COMMIT step, the table cannot be unlock and freezing my database. Should I swap the COMMIT and UNLOCK TABLES step?

According to the MySQL document:

  Beginning with MySQL 5.5.5, it is no longer possible to set
  @@session.sql_log_bin within a transaction or subquery. (Bug #53437)

As my understanding, 400K rows is not big enough, cannot find out why it locked my db?

Any ideas?


texts: MySQL not starting due to inability to write tmp files, but tmp directory seems to have correct permissions

texts:
The inability of mysql to write tmp files is apparently not that uncommon, but in my situation it seems like the permissions required should already be in place, so I'm wondering why mysql can't access it's tmp file location.

Just to make sure I'm clear that we're not talking about that also-not-uncommon apparmor permission problems here are the error message I'm getting:

/usr/sbin/mysqld: Can't create/write to file '/var/lib/mysqltmp/ib70AjJD' (Errcode: 13)
161031  0:20:12  InnoDB: Error: unable to create temporary file; errno: 13

I've already looked at the following possible solutions:

https://serverfault.com/questions/84890/cant-create-write-to-file-tmp-sql-xxxx-myi-errcode-13
https://stackoverflow.com/questions/8633373/mysql-cant-create-write-to-file-error13

What seems to be different about my situation is that the permissions to mysql's tmp directory seem to be correct, and if I switch the directory to /tmp instead, with seeming identical permissions, everything works fine.

Permissions for /var/lib/mysqltmp (the original config value; fails):

drwxrwxrwt 2 mysql   mysql   12288 Oct 30 23:44 mysqltmp/

Permissions for /tmp (updated by me; works):

drwxrwxrwt   5 root root  4096 Oct 31 09:44 tmp/

MySQL 5.5.53 is running as user mysql.  Obviously the owners of the two directories are different, but given those directory permissions that shouldn't be relevant, right? What's making this work using /tmp but not var/lib/mysqltmp?


texts: How to store data where various subcategories have specific columns?

texts:
I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below:

Product Table
  p_id
  p_title
  category_id   F Key

Category_Table
  c_id
  c_name

Sub_category_Table
  sc_id
  sc_name
  c_id      F Key

Now, the problem is, for example, I have 8 categories and about 60 subcategories with some common column names and some quite different like this:

Vehicle                           // a category
  Car                          // subcategory
      id,
      cat_id
      brand_id
      model
      price
      year
      insurance_validity_date
      fuel_type

  Motorcycle                          // subcategory
      id
      cat_id
      brand_id
      model
      price
      year

  Bus/Lorry                          // subcategory
      id
      cat_id
      brand_id
      model
      price
      year
      insurance_validity_date
      fuel_type

Now I'm merging all the same columns in one table named Vehicle and making a new table named car to store the insurance_validity_date, fuel_type columns and no table for Motorcycles at all like this:

Vehicle (= Motorcycle)                           // a category
      id
      cat_id
      brand_id
      model
      price
      year

 Car/Bus                          // subcategory
      id
      insurance_validity_date
      fuel_type
      vehicle_id

It means there is no need to create a Motorcycle table, and Car and Bus will be stored in a single table. Is it right?

Motorcycle is a Vehicle
Car is a Vehicle
Bus is a Vehicle

The vehicle_id value will identify whether the record is a bus or a car.

Here is a similar example for users. I've three types of users: admin, registered and guest with the following schema:

Person
  user
      id
      name
      user_name
      email
      contact_no
      password
      no_of_log_ins
      last_login_time
      registration_date
      city
      user_role_or_type_id

  business_owner
      id
      name
      user_name
      company_business_shop_name
      email
      contact_no
      address
      password
      no_of_log_ins
      last_login_time
      registration_date
      city
      user_role_or_type_id

   guest
      id
      name
      email
      contact_no
      city
      user_role_or_type_id

   admin
      id
      name
      email
      contact_no
      password
      no_of_logins
      last_login_time
      user_role_or_type_id

Now I'm merging all the same columns in one table named Person and modifying user, guest and admin to have type-specific columns like this:

Person (= Guest)
      id
      name
      email
      contact_no
      city
      user_role_or_type_id

   guest
     id
     Person_id

   registered_user (or admin)
      id
      person_id
      user_name
      password
      no_of_logins
      last_login_time

          admin
             id
             registered_user_id

          user
             id
             registration_date
             registered_user_id

          business_owner
             company_business_shop_name
             address
             registered_user_id

This model means:

Guest is a Person
Registered user is a Person
(i)   user is a Registered User
(ii)  business_owner is a Registered User

That's all what I think a solution for me. Is it a good solution? Would it not degrade the performance? Should I look for any other solution? I need some guidance.


texts:
I am a novice at database management and doing a small project on Feedback System.
My question is that I want to know that how i should store the data of feedback form. Suppose i have different feedback form for different subjects and each student submits the form so how i should store and manage the data?
 My feedback form may have around 20 questions which can be descriptive type also. Do i have to create table for each student, in that case my tables will be too large or i have to create table for each subject?


texts: List of variable defaults for InnoDB engine

texts:
I would like to list the defaults for the global variables that are innodb specific.

The problem is that the following commands doesn't list any innodb variables:

/usr/sbin/mysqld --no-defaults --verbose --help
/usr/sbin/mysqld --verbose --help

However I can see the runtime ones with:

mysql -e "show global variables"


texts: Break the link between a Database User and SQL Server Login

texts:
When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User.

This is normally achieved with one of the following procedures.

Deprecated sp_change_users_login Stored Procedure

use <database>
go
sp_change_users_login 'Update_one', '<datbase_user>', '<sql server login>'
go

ALTER USER Command

use <database>
go
ALTER USER [<datbase_user>] WITH LOGIN = [<sql server login>]
go

These commands will re-link an orphaned Database User to a corresponding SQL Server Login.

Question

Is it possible to break this relationship without deleting either the SQL Server Login or the Database User?

Reasons

Deleting the Database User will remove the permissions in the database.
Deleting the SQL Server Login will remove the password. (Hashed value; unknown to DBA)
I linked a Database User to a SQL Server Login that is a Windows System Account.

Research

I have had a look at the sys.database_principals and sys.server_principals DMVs, but they are not modifiable.


texts: Reading from subscriber during Transactional Replication

texts:
When using Transaction Replication, are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

i.e. can selects be made against tables to which updates are being applied?

Will performance of these selects suffer whilst transactions are being applied?


texts:
We have issues connecting to our PostgreSQL server. Sometimes nobody can connect, sometimes some can and some can't. We have no idea why.

The PostgreSQL server is on a virtual machine with 4GB of memory. We have 3 poeple who want to connect to that server. Could it be possible that number of connections are over the default max_connection?

Error message:

  could not connect to server: Connection refused Is the server running on host "192.x.x.x" and accepting TCP/IP connections on port 5432?


texts: SQL Server 2012 Install error "The instance selected for installation is already installed and clustered on computer..."

texts:
I am trying to setup a new environment for SQL Server 2012 AlwaysOn. I inadvertently used some SQL Server 2008 config files initially to do the installs but ran into too many problems. I un-installed SQL Server using program features but apparently that did not remove everything.

Now when I try to use the install wizard, I get to the "Installation Configuration Rules" screen and am presented with a failed status for rule "Existing clustered or cluster-prepared instance". If I click on the "failed" link, the dialog box says

  Rule 'Existing clustered or cluster-prepared instance' failed. The instance selected for installation is already installed and clustered on computer SQLNode1. To continue, select a different instance to cluster."

The problem is I absolutely need to use the same instance that I had tried to previously remove. How can I ensure the previous instance has been removed so I can attempt this install again?


texts: Equivalent of stored procedures for other database systems?

texts:
I am trying to compare centralized, distributed database systems and blockchains in several points and got stuck when searching about 'stored procedures'.

So far, I have thought that this is a general term for scripts that are triggered given a certain event, i.e. the equivalent to 'Smart Contracts' in blockchains. However, I have realized that this term is only used for relational database management systems.

What would be the equivalent to achieve automation within other database systems? I couldn't find anything useful through search engines.

I am trying to find a means to implement some market matching logic (e.g. continuous matching through an order book) into a database system. I thought that would be generally achieved through stored procedures? Or is there another way?


texts: Cannot add a column to an InnoDB table in Mariadb

texts:
I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64).

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| current_version | varchar(20)  | NO   |     | NULL              |                |
| last_updated    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| key             | varchar(256) | NO   |     | NULL              |                |
| language        | varchar(20)  | NO   |     | NULL              |                |
| text            | text         | NO   |     | NULL              |                |
+-----------------+--------------+------+-----+-------------------+----------------+

When I try to add a column, the client hangs like so:

MariaDB [pkppln]> alter table terms_of_use add column `weight` int(11) not null default 0;
Stage: 2 of 2 'Enabling keys'      0% of stage done

The server continues to respond to other queries, but the client does nothing. I can kill the query with ctrl-C. The alter table command works in my dev environment, but seems to be failing here.

How can I fix this, and add the column?


texts: WCS8 Solr Preprocess failed : Error SQLCODE=-601, SQLSTATE=42710, SQLERRMC=WCSSTGAP.XI_CATENTRY_PRICE_0

texts:
Our Solr Pro-process is failing with below error:

  [2018/08/28 14:31:36:104 CDT] 00000001 W
  com.ibm.commerce.foundation.dataimport.preprocess.AbstractDataPreProcessor:createDBTable(Connection,
  String, String) create XI_CATENTRY_PRICE_0 with error: DB2 SQL Error:
  SQLCODE=-601, SQLSTATE=42710,
  SQLERRMC=WCSSTGAP.XI_CATENTRY_PRICE_0;TABLE, DRIVER=4.19.49

I suspect that DROP table XI_CATENTRY_PRICE_0 is failing and ultimately causing the creation of the existing table.

How could I resolve this?


texts: associate different credit cards with every purchase

texts:
I have a schema in which I store data regarding customer's credit cards and I want to find a way to associate each order (can have multiple payment entries -partial payments-) with its the selected credit card in the checkout funnel
So far I have
Order
-----
id
status (started, completed)

customer_cards
----
id
customer_id
card_id (entity card_token_id)
other card_data

Payment_entries
-----
id
payment_token (charge_id from entity, like stripe)
order_id (FK)
payment_type_id (FK to payment_type) (in this case id for stripe entry in payment_type)
status

I would like to know which customer_cards has been used to place an order (first order-payment_entry entry)
I've been thinking on adding a new column in Payment_entries that stores the card_id or customer_cards id of each payment entry but I'm worried It would not scale if we add a different payment_type
Other idea is to add a table with the relation order_id - customer_cards but it sounds a bit redundant to me.
What do you folks advise?
Thanks


texts:
I am reading 'Pro SQL Server 2019 Administration' book from Carter, and when detailing HA and DR options, 'replication' is not mentioned:

Why is it omited? Will Replication be deprecated soon?


texts:
I have 3 tables and I want to know with one query sum of row's number in 3 tables

I tried 2 different queries but it they give only the number of rows each table and I c'ant read with,

     return int.Parse(cmd.ExecuteScalar().ToString());

the sum of rows of 3 tables.

I must read by reader and get the sum

Here are 2 queries I used:

    SELECT  (
        SELECT COUNT(*)
        FROM   prodbiscuit WHERE status =2
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   prodganache WHERE status =2
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   prodmacaron
        ) AS count1
        FROM   dual

And the other query which give the same result:

       SELECT COUNT(DISTINCT nom) as rows FROM prodbiscuit WHERE status=2

       UNION SELECT COUNT(DISTINCT nom) AS rows FROM prodganache WHERE status=2

       UNION SELECT COUNT(DISTINCT nom) AS rows FROM prodmacaron

How to get real sum of rows of tree tables with one query?

Should I use absolutely reader ?


texts: How to call a windows program in mysql prompt in windows

texts:
I want to call an OS level commands from mysql prompt within Windows command line tool.

In Unix* there is a system command available to execute OS commands within MySQL prompt.

Any way of dong the same in windows or even in workbench as well?

The objective is to invoke a OS command within a mysql prompt.


texts:
I would like to get the output of a particular Query from the postgresql server having joins of a multiple Tables with one to many relations in the json format to a particular file.
The Query output should be an actual json tree like below and not a flat join of master + child :-
Master Record 1 (master field 1, master field 2)
              { array
                             Child Record 1 {child field 1, child field 2, ….)
                             Child Record 2 { child field 1, child field 2, ….)
               }
Master Record 2 (....)
                Child Record 1
                Child Record 2

Please let me know how to achieve this in the Postgresql 11.
SQL Script
CREATE TABLE public.book
(
    pk_book_id integer NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    isbn character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT book_pkey PRIMARY KEY (pk_book_id)
);

CREATE TABLE public.author
(
    pk_author_id integer NOT NULL,
    fullname character varying(255) COLLATE pg_catalog."default",
    mobileno character(10) COLLATE pg_catalog."default",
    fk_book_id integer,
    CONSTRAINT author_pkey PRIMARY KEY (pk_author_id),
    CONSTRAINT author_fk_book_id_fkey FOREIGN KEY (fk_book_id)
        REFERENCES public.book (pk_book_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

INSERT INTO public.author(
    pk_author_id, fullname, mobileno, fk_book_id),
    (100, 'John Matthew', '98927828', 1),
    (101, 'Mark Knight', '99875528', 1),
    (103, 'Lara Croft', '99872628', 2),
    (104, 'Brad Show', '567982', 2)

INSERT INTO public.book(
    pk_book_id, name, isbn)
    VALUES (1, 'Hot Deals', 'ISBN89644'),
    (2, 'Summer Tales', 'ISBN405987');


texts: Is it possible to use the log of tracking changes to revert a table to a previous state in PostgreSQL?

texts:
This thread (Track all modifications to a PostgreSQL table) discussed how to generate a log to track changes in PostgreSQL.

How to use the log to revert a table to its older version programmatically?


texts: Can you Schedule a job inside another job?

texts:
I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a locking situation where two jobs are waiting for each other to finish.

The built in retry wont work because they are considered running while waiting to retry. I could just error out and have the job run every 10min checking when it was last run successfully and if anyone else is running. Then run or error again, but that could lead to jobs happening way off scheduled.

I have looked around but can't find anything, so I don't know if it is even possible but can you set up a one time schedule, like run this job at this time from inside another job?

Or is there a better way of doing this that I have overlooked?


texts: I have this as my proposed structure for task management module I had been working on. I think I can pull off viewing projects I create with the above schema, however I do want to add the ability to assign tasks to other user accounts[table has user_id as PK]. I am seeking some advice as to how to move forward from here to add that ability to assign tasks to users.


texts: Explain this statement about filtering in SQL Server vs MySQL

texts:
My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am apprehensive to make this move. Basically - our IT team is under the impression that MySQL is free and comes with only a few syntax changes.

I have spent the day reading up on the differences and honestly finding that they seem to be very similar. If I am going to make a case for MS SQL Server - then now is the time to do it. One key detail - we are an analytical company and our SQL queries are often complex - so I need to make sure that our RDBMS of the future is up to par.

I have encountered the following statement multiple times (about filtering) and I need a translation. My interpretation of this is that it is not possible to use a single query to join tables from differing databases. However I have seen youtube videos where people do just that in MySQL. Please help me understand this difference and whether it is important or not.

  MySQL allow users to filter out tables, rows, and users in a number of ways. But it requires users to filter out the tables, rows, or users by individual databases. While filtering the data, the developers have to filter database tables individually by running multiple queries. On the other hand, SQL Server enables developers to take advantage of row-based filtering. The row-based filtering option filters data on a database by database way. Also, the filtered data is stored in a separate distribution database. Hence, it becomes easier for programmers to filter multiple rows without considering the number of databases.

  - A Comparison between MySQL vs. MS SQL Server

Greatly appreciate your help with deciphering the quoted statement as well as any Pro/Cons you see for this move. Thanks!


texts: How to sort query filename output like Windows explorer?

texts:
i would like to order my query output the way Windows Explorer does.

First special characters like _, then the numbers, and the letters last.

_ttt.bmp
0.gif
bbb.png


texts: How to efficiently a query to return rows plus a summary

texts:
I need to write a query for a SQL Server 2012 database that will return rows to be presented to a search results page, this page has 12 filters that the user can use to narrow the results.

I create the query that returns the rows dynamically, then I use this same query as a sub query for all the summary queries that I need. The final piece becomes a pretty big string that gets sent to the server just once, it returns a multiple result set, instead of multiple calls,  but I think there should be a more efficient way for doing this.

Right now it seems to be working ok, but I'm not sure what would happen when it goes live and hundreds of users starts to hit it.

Any suggestion or code samples would be very appreciated.


texts: Are relay logs necessary for replication?

texts:
I'm trying to understand when/where using relay log is beneficial. According the documentation, mysql captures and writes events from the master's binary log as the result of the I/O thread. Then, the SQL thread executes the statements from the slave relay log.

Currently, I have the relay logs disabled on the slave but it hasn't affected replication. It still performs well.

If replication requires the SQL thread to read statements from the relay log so they can be executed, why is it still working without a relay log?

Thanks


texts: Total number of DML and DDL operations performed in a table in oracle

texts:
I would like to know the total number of operations (DDL and DDL) performed on a table in oracle dabatase since it was created.

Is there a query for it?


texts: Single query to get only newest entry of grouped redundant entries

texts:
A table files looks like:

id, file_name, vendor_id, created
1,  z.txt,     2,         2011
2,  z.txt,     2,         2011
3,  a.txt,     1,         2016
4,  a.txt,     1,         2016
5,  a.txt,     1,         2016
6,  b.txt,     1,         2015
7,  y.txt,     2,         2015
8,  x.txt,     2,         2014

There is an index on: file_name, vendor_id and created.

I want a list of the latest files (newest_file) of each vendor_id in a single query.

SELECT vendor_id, SUBSTRING_INDEX(GROUP_CONCAT(file_name ORDER BY created DESC), ',', 1) AS newest_file
FROM files GROUP BY vendor_id

This query works perfect, but is very slow. Is there a faster alternative in a single query?

CREATE TABLE `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `file_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `vendor_id` int(11) NOT NULL DEFAULT '0',
  `description` mediumtext CHARACTER SET utf8,
  `color` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `file_name` (`file_name`),
  KEY `created` (`created`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1


texts: Alternative of replication in internet disconnection period of time

texts: I'm looking for an alternative of merge replication that work without internet connection, something like red gate sql data compare but not in uniq direction.


texts: MySql - Get all children in JOIN if one child matches a condition

texts:
I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below:

SELECT * FROM Parent p
INNER JOIN Child c ON c.parent_id = p.id
WHERE c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'

This query returns only the child with name containing abc. I want to get all siblings of this child as well. How can i do that?


texts: Mongo Atlas connection error type=UNKNOWN, state=CONNECTING

texts:
Yesterday I've seen some strange behavior that my application can't connect to Atlas.
This has never happened before for about 1 year, and there was no application deploying going on.
The error that the Sentry caught was
org.springframework.dao.DataAccessResourceFailureException:
Timed out after 30000 ms while waiting for a server that matches WritableServerSelector.
Client view of cluster state is
{
  type=REPLICA_SET,
  servers=[
    {
      address=mydb-00-00.mongodb.net:27017, type=REPLICA_SET_SECONDARY,
      TagSet{[Tag{name='nodeType', value='ELECTABLE'}, Tag{name='provider', value='AWS'}, Tag{name='region', value='MY_AWS_REGION'}, Tag{name='workloadType', value='OPERATIONAL'}]},
      roundTripTime=11470.4 ms, state=CONNECTED
    },
    {
       address=mydb-00-02.mongodb.net:27017, type=UNKNOWN, state=CONNECTING
    },
    {
       address=mydb-00-01.mongodb.net:27017, type=REPLICA_SET_SECONDARY,
       TagSet{[Tag{name='nodeType', value='ELECTABLE'}, Tag{name='provider', value='AWS'}, Tag{name='region', value='MY_AWS_REGION'}, Tag{name='workloadType', value='OPERATIONAL'}]}, roundTripTime=15590.7 ms, state=CONNECTED
    }
  ]
nested exception is com.mongodb.MongoTimeoutException ...

This behavior went on for about 30 minutes.
When I see metric from mongodb atlas, mydb-00-02 was the primary replica set at the time of the incident. After the incident, mydb-00-01 became the primary replica set.
Could this happen during the reelection of the primary node? There were numerous reelections before, but connection error never happened.
I've searched for this issue, but most of the issue was from not whitelisting IP on Atlas, which is not the case for our application.


texts:
I have MySQL Database and its 1.5TB. I need to setup replication for this. I have 32core CPU and 250GB Memory. The problem restore is taking more time even I used mydumper/myloader to backup and restore.

Here is my MySQL Setting:

innodb_buffer_pool_size =235G
bulk_insert_buffer_size = 5G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 22
innodb_adaptive_hash_index = 0
innodb_old_blocks_pct = 45
tx_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0

In myloader:

120 threads running to restore.
100000 queries per transaction.

But still its resorting more than a day. Is there any possibilities to restore


texts: How to enable/disable SQL agent jobs during fail over (AlwaysOn)

texts:
Note: this is not duplicate question.

I have setup AlwaysOn. I have created SQL agent jobs manually on both the nodes. right now all the active jobs are running if node is PRIMARY.

Challenge

My DBA will disable few SQL agent jobs manually in only one node based on business requirements. Now how does it synchronizes to node 2?  Will DBA have to manually disable these related jobs in all the secondary nodes ?

I am thinking of creating a table (this table will be part of availability group) where I maintain the state of the jobs. and during fail over I am planning to run the script to read the data from this table and set the state of the jobs on other nodes. Is it right way? or do we have any recommended steps?

Kindly suggest


texts: Databases outside of AlwaysOn availability group stuck in RESTORING status

texts:
I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT belong to an availability group are stuck in a status of "Restoring" on the secondary server. I have started from scratch several times, and the same thing happens to these databases.

Is this normal behavior?

I was assuming that databases that don't belong to an AG would simply not show up on the secondary server.


texts:
I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an infinite loop, but only if I execute certain statements in it. If I just print out the values from the FETCH, it works perfectly. Here is the code.

DECLARE @id bigint;
DECLARE @table_name varchar(max);

DECLARE st CURSOR LOCAL FAST_FORWARD FOR
SELECT ID, TableName FROM SearchTables WHERE CustomerID IS NULL

OPEN st
FETCH NEXT FROM st INTO @id, @table_name
WHILE @@FETCH_STATUS <> -1
BEGIN
    IF(OBJECT_ID(@table_name) IS NOT NULL)
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

    PRINT CAST(@id AS varchar(max)) + ' ' + @table_name;

    FETCH NEXT FROM st INTO @id, @table_name;
END

CLOSE st
DEALLOCATE st

If I comment out these lines

    IF(OBJECT_ID(@table_name) IS NOT NULL)
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

PRINT outputs all of the IDs and table names. If I don't comment them, all I get is the first row over and over until I cancel the query. I also tried changing the IF line to EXEC('DROP TABLE IF EXISTS ' + @table_name) but that didn't work either.


texts: deleting small number of rows hangs in tsql database

texts:
So I'm just trying to delete some bad data from a one of our test databases. I've identified the rows to be deleted, and have written a simple query to handle this.

However, when run, the query hangs on 4% complete, and just sits there for hours. Our databases are hosted on Azure - For this test database we're running a Standard SO with 10 DTUs.

What am I missing here?


texts: How to deal with a table that exists in the ALL_OBJECTS table but cannot be selected

texts:
I am seeing a weird behaviour in my Oracle DB schema around a 'missing' table.

The end goal is to gather statistics for all the tables that our team uses. All those tables have a common prefix - rca.

So, I am fetching the list of tables using:

SELECT *
FROM ALL_OBJECTS
where OBJECT_NAME like 'rca%'
  AND OBJECT_TYPE = 'TABLE';

This query returns a table called rca_related_materials.

We're using a function developed by some other team that does the gather stats job for us. We just pass the owner_name and the table_name to that function.

Calling that function for this table name or running

analyze table rca_related_materials compute statistics for table;

or even,

select *
from rca_related_materials;

returns the below Error report

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:

How can I get this entry removed from the ALL_OBJECTS table?

PS: I see the same entry coming up in USER_TABLES and DBA_TABLES also.


texts: MySQL 5.6 - Query Performance Issue. Searching for Slug

texts:
I've a case where I don't know how to better the performance of this query.

This is a simple query to the Geonames.org database.

The query:

SELECT name, latitude, longitude FROM geoname
WHERE
slugify(geoname.name) = 'lisboa'
and geoname.country = 'PT'
and geoname.fclass in ('A', 'T')

The Explain:

id, select_type,   table,   type,     possible_keys,     key, key_len,    ref,    rows, Extra
 1,      SIMPLE, geoname,    ref,    fclass,country, country,      11,  const,   73462, Using index condition; Using where

I know that the problem is this where clause:

slugify(geoname.name) = 'lisboa'

I cannot index this. The slugify() is a MySQL function that slugify the geoname, for example New York becomes new-york.

What options shoul I consider to have a better performance in this situation. What can I do?


texts: Many-to-many relationship when one entity is without attributes

texts:
I'm interesting in mapping many-to-many relationship to relational model.

I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-many relationship. But what happens if one of the entities doesn't have attributes? Do we really need a third table?

For example, let's consider entities user and cluster. One user can belong to multiple clusters and one cluster can contain multiple users. And we could create a third table such as "belongs-to". But do we really need that if cluster doesn't have any attributes? Could we, in that case, put the user_id key as foreign key in cluster table and consider that pair (user_id, cluster_id) as a composite key?

I see no gain of creating another table. If cluster had attributes it wouldn't make sense to repeat all the information for each cluster. But if it doesn't have attributes than the same amount of information is saved in both cases, creating a separate table or putting the user_id key in cluster table.

I'm confused, what is the right thing to do?


texts: My requirement is to execute a system command like (ls) or C program when a trigger executes. Is there any way to create a trigger function to solve this problem.


texts:
I am attempting to run a query using MAX() but my issue is that if the field contains a NULL value the data returns nothing.  Updating the table is not an option, so how should this query be re-written to return 0 when NULL?

Sample DDL below - and I hardcoded a WHERE clause that returns nothing just for illustrative purposes, ideally I want to return all usernames and the highest logincount for the user

CREATE TABLE Test
(
 username varchar(50) not null,
 logincount int,
 logindate Date
);

Insert Into Test (username, logincount, logindate)
VALUES
('er11', 1, '2017-01-01'), ('er11', 2, '2017-01-02'), ('ff12', NULL, NULL)
,('jb88', 1, '2017-01-09');

With maxlogincount As
(
    select max(logincount) As "MaxLoginCount"
    ,username
    FROM Test
    GROUP BY username
)
Select
username
,logincount
,logindate
FROM Test t
inner join maxlogincount mlc
ON t.username = mlc.username
And mlc."MaxLoginCount" = t.logincount
WHERE t.username = 'ff12'
Order by t.username asc


texts: Is possible to backup database online using rsync?

texts:
Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the backup would not consistent.

Actually I am not sure why the backup using rsync is not safe. Could anyone explain me the underlying of mysql for the reason ?


texts: Postgres - Combine similar queries with extra where clause

texts:
I am trying to combine a number of fairly time consuming queries into one (if possible)

The database contains race-track data that records the times for laps completed by drivers (or users here), the database contains the following tables, simplified for brevity:

user_sessions:

user_id (fk)

session_id (fk)

sessions:

session_id (pk)

session_date

track_size

laps:

lap_id (pk)

car_type

lap_number

rest_time

I have some queries that leverage Postgres window functions to find the highest number of consecutive laps for a user in the same type of car, each query returns the session_id from the highest record and total distance covered for those consecutive laps

Both queries are exactly the same however one includes an additional where clause on the inner select (to exclude laps with rests).

My question is, can I combine this into a single query to improve performance? whereby the combined query returns two results (one including rest laps, and one that doesn't)?

SELECT x.sess_id, round(count(x.*) * x.size,2)
    FROM (
        SELECT l.session_id as sess_id, l.car_type, s.track_size as size,row_number()
        OVER (ORDER BY l.session_id, l.stroke_type, l.lap_number) - l.lap_number AS grp
        FROM laps as l, user_sessions as us, sessions as s
        WHERE l.session_id = us.session_id
        AND us.user_id = :userId
        AND l.session_id = s.session_id
        AND CAST(l.car_type as text) = CAST(:car_type as text)
    ) x ";
    GROUP  BY x.sess_id, x.car_type, grp, x.size
    ORDER  BY count(x.*) DESC LIMIT  1

Second query

SELECT x.sess_id, round(count(x.*) * x.size,2)
        FROM (
            SELECT l.session_id as sess_id, l.car_type, s.track_size as size,row_number()
            OVER (ORDER BY l.session_id, l.stroke_type, l.lap_number) - l.lap_number AS grp
            FROM laps as l, user_sessions as us, sessions as s
            WHERE l.session_id = us.session_id
            AND us.user_id = :userId
            AND l.session_id = s.session_id
            AND CAST(l.car_type as text) = CAST(:car_type as text)
            AND l.rest_time <= 0
        ) x ";
        GROUP  BY x.sess_id, x.car_type, grp, x.size
        ORDER  BY count(x.*) DESC LIMIT  1

(I am using PostgreSQL 9.0)


texts: I am struggling to by pass my startup code in MS Access using the SHIFT + ENTER keys. How else can I accomplish this?


texts:
I installed the evaluation version of SQL Server on Ubuntu, which is free for 180 days.

How can I check how many days I have left in the trial period?

Does that edition have any limitations, such as storage or CPU?

How can I enter a serial number to activate SQL Server before (or event after) the 180 days?


texts: MySQL: Add column to unique index or add new index?

texts:
For a MySQL database, I have an InnoDB table name user_metrics with the following columns:

   id INT(11) NOT NULL AUTO_INCREMENT,
   userid INT(11) NOT NULL,
   name VARCHAR(32) NOT NULL,
   value DECIMAL(20,9) DEFAULT NULL
   valueclass INT(11) DEFAULT NULL

For this table, I currently have a primary key (on id) and a unique index on (userid,name). For one particularly heavy select query, I need an index on (userid,name,valueclass).

I could create a new index or add the valueclass column to the unique index I already have. As far as performance of the select query is concerned, it doesn't matter. There also doesn't seem to be any significant difference in disk space or memory usage. Mutations to the table would be a bit slower, but it's essentially write-once-read-many.

Are there any good reasons why I should NOT just add a column to the unique index I already have?

The select query has some joins as such:

SELECT      `name`
        ,   AVG(`valueclass`) AS `avg`
        ,   STD(`valueclass`) AS `std`
FROM        `user_metrics`
WHERE       `valueclass`    IS NOT NULL
    AND     `userid`    IN ([list of userids])
    AND     `name` IN ([list of names we want])
GROUP BY    `name`';

Basically, it tries to get some statistical information and only needs the three columns (so the entire query could be run on the index).


texts: How to use mysql sandbox to build s - m - m -s topology?

texts:
I want to build this topology

 M1  <->   M2
 |         |
 S1        S2

I've checked sandbox --help and it seems that it can only build:

M  ->  S

make_replication_sandbox mysql-5.6.tar.gz

M  <->  M

make_replication_sandbox  --master_master mysql-5.6.tar.gz

M  <->  M  <->  M  <->  M  <-> (circular)

make_replication_sandbox --circular=4 mysql-5.6.tar.gz

But how can I build

 M1  <->   M2
 |         |
 S1        S2


texts: RDS (MySQL): Swap usage growing but plenty of freeable memory?

texts:
We run a db.m4.10xlarge instance on Amazon's RDS. It runs 3 fairly busy databases and has a replica instance connected to it.

Over the last few weeks I noticed the swap usage has been steadily rising from 0 to about 80MB now. I don't know why it's using the swap space at all when there's plenty of free (freeable?) RAM. I've included charts from the last 2 weeks below:

The same thing is happening on the replica also, which is another concern of mine as our replica lag is continuing to grow in a similar manner.


texts: No suitable driver found Orientdb jdbc driver

texts:
I have some problems with Orientdb driver for jdbc.
i´m using Netbeans, i have include the library JDBC+OrientDB libraries (orientdb-jdbc-2.0.8-all.jar) in the directory and in netbeans, i have also include the connector in the proyect (i have include the connector in the Classpath),
but i still have the same problem :

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:orient:remote:localhost/GratefulDeadConcerts
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:208)
at Aconexion.AConexion.main(AConexion.java:15)
Java Result: 1
BUILD SUCCESSFUL (total time: 0 seconds)

my code:

package Aconexion;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.orientechnologies.orient.jdbc.OrientJdbcConnection;
public class AConexion {
public static void main(String[] args) throws SQLException {

Properties info = new Properties();
info.put("user", "admin");
info.put("password", "admin");
Connection conn = (OrientJdbcConnection)
DriverManager.getConnection("jdbc:orient:remote:localhost/GratefulDeadConcerts", info);
try{

Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver");
Connection connection = (OrientJdbcConnection)
DriverManager.getConnection("jdbc:orient:remote:localhost/GratefulDeadConcerts", info);
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}


texts:
I have a SQL server A and B which is production databases. The DR of production 'A' is production db 'B' and DR of production db 'B' is production db 'A'. DR feature that is used is log shipping. All in the same location.
Now, what are the very critical issues that I could face in case of disaster?


texts: Oracle: Helping hand writing this query. Do I need a sub query here?

texts:
I have the below query that returns the number of orders created on a particular customer number per month for the past 2 years.

SQL

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1
             ELSE 0
           END) AS val_2013,
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2014 THEN 1
             ELSE 0
           END) AS val_2014
FROM   orders o
WHERE  o.customer_no = '0'
       AND EXTRACT(year FROM entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM')

Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01        201        205
02        246        235
03        286        243
04        234        206
05        251        161
06        217        187
07        239        210
08        190        204
09        206        224
10        266        175
11        171          0
12        133          0

12 rows selected.

Question

The query above returns the number of orders but I also want the value of these orders in the same format as above.

I have a table order_totals which has order_final_total but there is no customer number column in this table. This is the part that is confusing me. Instead of the SUM of the number of orders, I need the SUM of the order value but I don't understand how to get this for just the orders on a particular customer number.

Expected Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01     9201.42    3467.67
02     1287.94    7819.25
...

Do I need to use a sub query, to first get the order numbers?

I've only been using Oracle for around 2 months so any help is appreciated!

50% there query

Ok I've come up with the below query that shows the value of these orders over the past 2 years split in to months but in a single column. I couldn't work out how to split it by year as the above query. Any ideas?

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(t.order_final_tot)
FROM   orders o,
       order_totals t
WHERE  o.order_no = t.order_no
       AND o.customer_no = '0'
       AND EXTRACT(year FROM o.entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM')

Results

TO SUM(T.ORDER_FINAL_TOT)
-- ----------------------
01              222333.09
02              216587.53
03              257628.37
04              245838.47
05               227823.5
06               241722.9
07              270359.57
08              277249.43
09              245064.45
10              227929.83
11               85159.13
12               56982.31

12 rows selected.

@ypercube's answer

I get:

MO ORDER_TOTAL_2013 ORDER_TOTAL_2014
-- ---------------- ----------------
03                0            88.26
05                0             45.2
06             16.1            23.75
07                0            69.37
09                0           116.68

Why do I only get months 03, 05, 06, 07 and 09?


texts: How can I track the last modified user of a file in an SQL Server 2012 FileTable

texts: Other than through a custom interface to store/fetch files using the old filestream way, is there a way to log which user modified a file opened directly from the share exposed by Sql Server 2012?


texts: Operation System error 665 with VLDB backup (The requested operation could not be completed due to a file system limitation)

texts:
Has anyone seen the following error while doing backups? We have tried different LUNS from different storage pools and are seeing the same result.

Here is the error message:
Operating system error 665 (The requested operation could not be completed due to a file system limitation).

NTFS Compression is not enabled on the drives. This failure usually comes when the backup is about half completed. I believe when the backup file size is extended again.  We are striping against eight files. The database is 60+ TB. I also saw some references to file fragmentation and I see that these drives show 0% percent.

I also saw several references to DBCC CHECKDB and sparse files. We are just doing a full backup and not any DBCC CHECKDBs. The goal would be to do this on a secondary server until any errors are recorded.


texts: Is it good idea to use RAID 1 mirroring to back up database content

texts:
I'm working on web hosting project. The planned system structure is going to be like this:

one main hard-drive will contain the binary of mysql.
A second hard-drive will contain the datadir
the third hard-drive should contain a backup of this mentioned datadir.

I can only think of two options here either replication using 2 instances of MySQL servers or just using RAID 1 mirroring only the datadir.

I would go with mirroring but I'm kind of suspicious of using this method and my main frightening thought is what if I lost the main hard-drive that contain the binary of mysql does setting new version will cause my a problem .. any ideas suggestions will be great full thanks

I should mention that using INNODB is a constraint


texts:
Server Configuration:

MySQL 5.051a-community-nt-log Community Edition installed on Windows Server 2003 Standard Edition SP1.

This MySQL Server is part of a client's WAMP installation

Problem:

MySQL was originally using the C:\WINDOWS\TEMP directory for temp files. From time to time, the client would get the following error when trying to access data in a table in phpMyAdmin.

"#1 - Can't create/write to file 'C:\WINDOWS\TEMP\#sql_1760_0.MYD' (Errcode: 17)".

Which would require a restart of MySQL to fix.

Thinking this might be a problem with using the C:\WINDOWS\TEMP directory, I created another directory that the MySQL service had read/write access to and pointed tmpdir to that directory in my.cnf.

tmpdir=e:/Program Files/wamp/bin/mysql/mysql5.0.51a/tmp

I restarted MySQL and accessing tables from phpMyAdmin worked fine for one entire day and then the following morning, phpMyAdmin started complaning about the same error.

Investigation:

There is more than enough disk space on both the C: and E: drives

Not seeing any other errors from MySQL.

Window's application logs don't mention anything about the error.

No information in MySQL's logs

Database in question is a mix of MyISAM and InnoDB tables.

I have not yet veririfed, if the inaccessible tables are only the ones in MyISAM.

Questions:

Has anyone seen this problem before with a WAMP MySQL 5.0 installation and know what might be causing it and or how to resolve this error?


texts: Rebuilding a single corrupt table with dual master replication

texts:
I have a replication setup with two MariaDB 10.2 instances set up in a dual-master configuration. On host A, a table has been corrupted somehow; whenever I try to read from it (including CHECK TABLE tablename) the process crashes.
May 05 13:04:05 hostA.my.internal mysqld[2666]: 2021-05-05 13:04:05 0x7f8b781c1700  InnoDB: Assertion failure in file /home/buildbot/buildbot/padding_for_CPACK_RPM_BUILD_SOURCE_DIRS_PREFIX/mariadb-10.2.36/storage/innobase/btr/btr0cur.cc line 376
May 05 13:04:05 hostA.my.internal mysqld[2666]: InnoDB: Failing assertion: btr_page_get_next(get_block->frame) == page_get_page_no(page)

I'm planning to take host A offline, and use innochecksum to diagnose the problem.
If, as I suspect, I need to rebuild this table, what is the quickest and safest way to go about it? I know I could repopulate host A's entire database from a dump of host B and rebuild replication from scratch, but I am hoping to avoid the trouble if it's possible.
If I get a dump of the table from host B and try importing it on host A, then DROP TABLE and all those INSERT statements (about 1.6 million rows) will be replicated, causing a lot of traffic and interfering with live services accessing host B.
Tagging as MySQL as well, since this seems like it should be applicable to either.


texts: Maximizing throughput on bulk data processing

texts:
I'm using SQL Serverversion 14.0.3035.2

I have a pile of information that I need to process from table A and insert the modified data into table B. I have written an application to query the data, do some binary manipulation, and insert the results into the target DB.

I am convinced the slowdown is during the read.

When I first started running the application, It was pretty speedy. Towards the second half of the execution it's slowed to a crawl. The difference is the number of rows with the bit IsFetched = 1 in the Source and the number of rows in the target table

Both tables share a drive for the Data files, and also share a drive for the Log files (Dedicated disk for log, dedicated disk for data)

In order to maximize my throughput, I've made the application run batches in parallel. My query to get the data and mark it as 'touched' looks like this

UPDATE TOP(100) _s
            SET IsFetched = 1
            OUTPUT
                INSERTED.[Id],
                INSERTED.[BinaryData]
            FROM Source _s
            where _s.IsFetched = 0

The table I'm reading from has the schema

CREATE TABLE [dbo].[Source](
[SourceID] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL,
[IsFetched] [bit] NOT NULL,

CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
(
    [SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Source] ADD  CONSTRAINT [DF_Source_IsFetched]  DEFAULT ((0)) FOR [IsFetched]
GO

I have a non-clustered index on IsFetched.

CREATE NONCLUSTERED INDEX [Idx_Fetched] ON [dbo].[---]
(
    [IsFetched] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I'm writing to a table with the schema

CREATE TABLE [dbo].[Target](
[SourceId] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

As expected, there are locks on fetching the data. However, it seems like it is taking much longer than necessary to get the data. I've used this pattern before and achieved a much higher throughput (10's of thousands of rows per second). Right now I'm getting a maximum throughput of about 200-300 rows per second.The binary data isn't that big so I don't think it's a matter of reading too much data at once.

I've found changing the degree of parallelism and batch size doesn't do much to increase the speed but the fastest I can get is about 20 degrees of parallelism with 10 rows per transaction.

My table doesn't seem to be too fragmented but my disk seems to be the culprit


texts: Query to obtain cccurrences within a certain period of time

texts:
I've been looking for and thinking of a solution to the following but I cant come up with it, and hope someone can help me.

I have the following table:

ClientId    RecordDateTime
--------    -------------------
      1     2019-02-14 14:05:49
      1     2019-02-14 14:06:34
      1     2019-02-14 14:07:19
      1     2019-02-14 14:08:49
      1     2019-02-14 14:09:34
      1     2019-02-14 14:10:32
      1     2019-02-14 14:12:32
      1     2019-02-14 14:14:18
      1     2019-02-14 14:15:10

I need to count the amount of occurrences using a determined amount of minutes to split the occurrences. For example:

I set the limit to 1 minutes
First datetime is 14:05:49
Second datetime is 14:06:34, which has less than 1 minute difference with the previous one (14:05:49), so I consider it belongs to the same occurrence.
Third datetime is 14:07:19, which also has less than 1 minute difference with the previous one (14:06:34), so is still same occurrence
Now it comes 14:08:49, which has a difference with the previous one of MORE than 1 minute, so I consider its a new occurence.
Next 14:09:34, which has a difference of less than 1 minute with 14:08:49.

And so it goes.

At the end, the result I want is:

ClientId   Occurrence Start      Occurrence End
--------   -------------------   -------------------
       1   2019-02-14 14:05:49   2019-02-14 14:07:19
       1   2019-02-14 14:08:49   2019-02-14 14:10:32
       1   2019-02-14 14:12:32   2019-02-14 14:12:32
       1   2019-02-14 14:14:18   2019-02-14 14:15:10

This is just an example, but I have a lot of data, from multiple ClientIds.
Is there  way of doing this without using stored procedures and looping through each row?

Thanks in advance for any help


texts: Optimize series of queries that recycle the same filter

texts:
I have about 30 queries across 50 tables in total which each pull or aggregate information to be later all joined together. The original code provided used entirely temp tables, but it is difficult to maintain. A common theme across the queries is the filter in the WHERE clause which selects the records we want.

I tried to convert the temp tables into a series of chained CTE's and used the WHERE clause as a filter CTE which is inner joined to all child CTE's so that when that filter is changed, it cascades to all other queries. Also, when new queries are added, they simply need to join to this CTE and the filter is applied. The idea is we start with the base filter table with only the IDs of the desired records, then left join to append the measures we want.

The problem is that this has a huge performance degradation compared to the temp tables. We've sacrificed performance for consistency, modularity, and ease of maintenance. Is there any way to tweak the performance in our favor, though? The biggest hits seem to happen on those that use windowing functions to concatenate strings across multiple rows into one row per record.

I've never attempted something of this nature before, and CTE's seemed like the logical approach. What was once a 4-5 minute query is now taking 30 minutes.

How it looks with temp tables:

select
    ...
INTO
    #temp1
FROM
    ...
WHERE
    <repeated filter>
    AND
    <temp1 filter>
select
    ...
INTO
    #temp2
FROM
    ...
WHERE
    <repeated filter>
    AND
    <temp2 filter>

SELECT
    ...
FROM
    #temp1
        join
    #temp2
WHERE
    <repeated filter>

And with the CTE's:

WITH Filter AS (
    SELECT
        ...
    FROM
        ...
    WHERE
        ...
), Query1 AS (
    SELECT
        ...
    FROM
        ...
            INNER JOIN
        Filter
    WHERE
        <query1 filter>
), Query2 AS (
    SELECT
        ...
    FROM
        ...
            INNER JOIN
        Filter
    WHERE
        <query2 filter>
)
SELECT
    ...
FROM
    Filter
        LEFT JOIN
    Query1
        LEFT JOIN
    Query2


texts: Get all the queries executed in the last week in SQL Server

texts: How can I get all the queries executed on a SQL Server in last week.


texts: Putting foreign keys of several tables into the same column

texts:
I've never come across this situation, not sure how to go about it.

Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and the Asset tables. A portfolio can have either Asset 1, or Asset 2, or both, or none.

Can I do something like the bridge table in the image above, i.e. where the bridge table (example below) would contain in the same column, the foreign keys to two different tables


texts: NHibernate second level cache collection hydration takes too long

texts:
I have enabled query caching and entity caching in NHibernate to store large collections of objects, needed for repetitive operations. While I have minimized the number of hits to db, the performance benefit is suboptimal.

Loading entities from second level cache takes way too long. Does anyone know how to optimize this?


texts:
This question is a follow up on these two:

Auto rollback Explicit transactions after X amount of time
Database file …_log has a max file size set to x MB. If it runs out of space, the database will stop working

From the second one I find out that an careless SQL user might leave a very long running transaction and lead to allocating huge amount of disk for the log file.

The first one indicates that killing a transaction after a certain period of time can be achieved through a job, although this approach is not recommended.

If I am sure that none of the transactions opened against a database should not allocate more than a certain amount of log, is there a way to find out how much log is allocated for a SPID, so that can I kill it?

Shortly put, I am interested in capping max log file usage for a transaction.

Question: Is there a way to limit log file usage for a transaction in SQL Server?


texts: LOG BACKUP is TRUNCATING in Log Shipping

texts:
I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got failed continuously after T-log truncation.

I used the following query to find the history of backup taken for a DB.

select  top 60 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'ADMIN%'
order by a.backup_finish_date desc

As a result of above query, i able to find that LOCATION is NULL for truncated T-Log file. Due to this LSN Mismatch is occurring while performing restore.

We are unable to find that why it is truncating and appearing as NULL. Is there is any solution to stop truncating the T-LOG File.


texts: The GET_LOCK() and RELEASE_LOCK() functions are taking too long

texts:
We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke from our Java code (the queries inside the proc are given below).
We have been monitoring the execution time of these functions and found that at times it takes a millisecond to execute but, at other times, this takes around 400 to 600 ms. I have tried the following approaches but there hasn't been much of a difference:

Use "Do" in place of select with these functions.
Using an int data type of the key which we are using as lock string.
Decreasing the length of lock string.

I am using a timeout of 0 to avoid connections being locked.
Questions

Can anyone please suggest me a way to optimize this?
Is there a way of optimizing innodb buffer pool or something related to these configurations?

Please do let me know if any other input is required from my end.
Code
Please find below some proc code and stats for your reference:
get_Name_lock:

-- Using select

Select get_lock(Name,0) into c_Name_flag;

-- Using Do

Do get_lock(Name,0) ;

Stored proc:
release_Name_lock :

-- Using select

Select release_lock(Name) into c_Name_flag;

-- Using Do

Do release_lock(Name);

Request rate: (around) 10 requests/sec
MySQL version: 5.7.19-log


texts: How to speed up counting and filtering on table with a lot of columns and filter conditions?

texts:
I have TABLE_A in my database that has a lot of columns.

Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100

There are so many columns not because of denormalized design, but because each row represent Entity with a lot of properties (in this example - with 99 properties, first column is just an id)

My Application should do the following tasks:

Receive a set of filter conditions from the user
Execute a query in the database that filters the data on given conditions and then should count the result rows

There are no limits on the number of filter conditions:

there may be no filter conditions
there may be filter conditions on every column of the table
there may be filter conditions on some columns of the table (for example on 50 columns out of all)

So below is an example query that my application executes:

SELECT
    COUNT(*)
FROM
    TABLE_A
WHERE
    COLUMN_1 = 'test'
    AND COLUMN_2 != 'q'
    AND COLUMN_45 > 5
    AND COLUMN_45 < 511
    AND COLUMN_92 LIKE '%ddd%'
    AND COLUMN_98 > 1000

TABLE_A doesn't have any indexes - each column can be used for filtering and there are no sets of queries that are run much more frequently than the rest.

I also don't use any kind of cache on any level: insert and update operations happen not very often but more often than at least 2 query with the same filter conditions occur.

So in case of every query the sequential search is executed. It was not a problem earlier, but now the run-times of the query became unacceptable (number of rows in the table increased a lot).

So..

Is there any solution on how to speed up the execution of the task?

Any suggestions would be appreciated - I can change anything except one thing - I should use SQL. Currently I use PostgreSQL, but I would change DBMS if it will help.


texts: Simple left outer join leaving me with inner join

texts:
So I have a table with each record being a change to an object.

e.g. in the app

you open your project with a project id
in this project you edit an object name
hit save

that generates new record with datetime stamp and object name change value, related to the project id that you work on

tricky part is, project_id can be null. Still the object_id is unique. So I know from the object id, on which project I am.

table change
with object_id and datetime being a composite primary key.

e.g.

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
77        | 2014-02-24 09:55:40 | somevalue   | 88
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I want to get is all the records, that belong to project 10

that would be

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I tried and failed horribly is an inner join on the same table

I tried this:

SELECT  * FROM change
LEFT JOIN (SELECT * from change AS x WHERE x.project IS NULL) AS change2
ON change2.object_id = change.object_id
WHERE change.project = 10

This gives me only records with null elements for project id, like for an INNER JOIN.
I tried as well:

SELECT  * FROM change
LEFT JOIN change AS change2
ON change2.object_id = change.object_id
AND  change2.project is NULL
WHERE change.project = 10

Funny, it gives the same result (like an inner join would, e.g. only records with null as project)

What am I missing?


texts: Why late materialization is not triggered for insert queries?

texts:
Assume I have table t with columns c1,c2,c3. (not so many records, ~10k) and an empty table tmp_table with same structure.
Talking about columnar store, I want to understand when does it make sense to apply late materialization/projection.
I wrap query q2 inside insert and suddenly late materialization is not triggered anymore, why ?
select * from t where c2 = 4; -- q1: late projection is applied

insert into tmp_table (select * from t where c2 = 4); -- q2: late projection NOT applied

A similar question I found on: https://answers.sap.com/questions/699087/late-materialization-insert-processing.html
But the answer is revolves around OUT OF MEMORY exception while I have little data that fits inside main memory.


texts: Is it a good or bad idea to use "ON UPDATE CASCADE ON DELETE CASCADE" for foreign keys? Why does this mechanism exist at all?

texts:
I understand what foreign keys are, and have made a point of including them wherever they make sense for all my database tables that I design.

However, something which has always confused me is whether or not I should be explicitly setting the "ON UPDATE" and "ON DELETE" features (in lack of a better term). Example:

CREATE TABLE "test1"
(
    id              serial,
    referenceid     integer,
    FOREIGN KEY     (referenceid) REFERENCES "othertable" (id) ON UPDATE CASCADE ON DELETE CASCADE
)

This code goes out of its way to explicitly add the technically "unnecessary" part: "ON UPDATE CASCADE ON DELETE CASCADE".

Since this is not done by default, there must be a reason for this! After all, the default behaviour is always (or at least should always be) the most commonly needed behaviour:

CREATE TABLE "test2"
(
    id              serial,
    referenceid     integer,
    FOREIGN KEY     (referenceid) REFERENCES "othertable" (id)
)

In the test1 table, as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that the referenced records in the test1 table will either be updated or deleted. This seems, on the surface, like what should be the default behaviour.

In the test2 table, again as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that PostgreSQL will refuse to perform the query if there are records in test2 which reference the ones being modified.

I'm basically confused about the entire concept of "ON UPDATE" and "ON DELETE". Why would one ever want a query to be refused like that? And "CASCADE" isn't even the only option (besides none); there are multiple other values you can use which cause various behaviour (which I don't understand).

Since there is a stated relationship between the tables (through the foreign keys), isn't the whole point that you want them to remain consistent? So why you not want it to "CASCADE" if there are changes made to the "master" table?

This might be similar to how I could never understand why object-oriented programming had "security measures" in the code, disabling you from directly changing or retrieving an object's properties and being forced to go through "getters" and "setters". I mean, if something can execute queries in your database, isn't "all lost" anyway? They can just do:

DELETE FROM table1/table2 CASCADE

... or something like that.

The ON UPDATE/ON DELETE mechanism seems almost like the database engineers could not decide on the best behaviour and instead put this on the user of the product instead. For me, it adds a lot of confusion and anxiety.

It should be noted that I have used the "test2" style code many times in the past, only to realize that I cannot update or delete records where it made sense. That's why I started using "ON UPDATE CASCADE ON DELETE CASCADE" in the first place, after asking and learning about it.

So why isn't this the default and even the only behaviour for a database? Why would you ever want a query to update/delete your "master records" to fail?


texts: SQL Server - Varbinary Column - Extremely Slow Statistics Update

texts:
I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table had essentially "jammed" on an insert statement.

Checking sys.dm_os_waiting_tasks showed that the insert statement had triggered a statistics update, and that this statistics update was taking a very long time. (17 minutes).

Here's the statement we found running:

SELECT StatMan([SC0], [LC0]) FROM
   (SELECT TOP 100 PERCENT CONVERT([varbinary](200),
        SUBSTRING ([Data], 1, 100)++substring([Data],
        case when LEN([Data])<=200 then 101 else
        LEN([Data])-99 end, 100)) AS [SC0],
        datalength([Data]) AS [LC0]
    FROM [FileSystem].[FileData] WITH
    (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

There are roughly 2000 rows in this table, here's what it looks like:

CREATE TABLE [FileSystem].[FileData]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__FileData__Id__09DE7BCC] DEFAULT     (newsequentialid()),
[Data] [varbinary] (max) NULL,
[FileHash] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileSize] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [FileSystem].[FileData] ADD CONSTRAINT [PK_FileData] PRIMARY KEY     CLUSTERED  ([Id]) WITH (STATISTICS_NORECOMPUTE=ON) ON [PRIMARY]
GO

We're aware this table is pretty weird, and we are careful to only every address it with a clustered index seek.

For the moment, we've simply disabled automatic statistic generation for this table, but I'm wondering if that's really best practice. Will performance eventually become a problem without up-to-date statistics (bear in mind, this table is only every addressed by it's clustered index)?

Update:
Okay I'm pretty sure we've worked out what was causing the statistic to be generated:

GO CREATE PROCEDURE [FileSystem].[FileData_AppendNewData_Easy]
    (@fileDataId  uniqueidentifier )
WITH EXECUTE AS CALLER AS
BEGIN
   declare @testValue varbinary(max);

   set @testValue = 0xabcedf012439;

   Update FileSystem.FileData
   set Data.Write(@testValue, null, null)
   where Id = @fileDataId ;
END

This is a simplified version of the procedure that was causing the issue, it seems Data.Write causes some kind of implicit predicate on the data column? Well that solves that mystery, although I'm still not sure what impact disabling the statistics for this table might have, can anyone comment on that?


texts: What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#?

texts:
How can I do the following with C#:

Completely backup an existing database.
Partially backup an existing database.
Completely restore an existing database from a backup file.
Partially restore an existing database from a backup file.

My database saves images on the HARDDISK sometimes I need to backup and restore the database without the stored Images (might be 50 GB).

I need partially backup that execluding the FILESTREAM data
(The saved files should be not included in the backup file).

I need partially restore that retrive the database without FILESTREAM data (The stored files should be not restored).

What is the best practices to do that?


texts: Subset of Unordered Pairs of Distinct Primary Keys With Normalization

texts:
I am currently in the process of designing a database. I have one table as follows:

CREATE TABLE Set (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- other values
);

Now I wish to create another table that will store unordered pairs of distinct primary keys from the Set table, without sacrificing normalization. My first idea was:

CREATE TABLE Pairs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    valueone INT NOT NULL,
    valuetwo INT NOT NULL,
    -- Appropriate foreign key constraints for valueone and valuetwo
);

But this has the problem that I could store, for example the pair valueone=1, valuetwo=1 which I do not want as they are not distinct, or I could store either both or just one of valueone=1, valuetwo=2 and valuetwo=2,valueone=1 which (depending on how I interpret the data) would clearly be de-normalized. What is a natural, normalized way to store this fairly simple piece of information?


texts: phpMyAdmin Error After Upgrade to 3.5.8.1

texts:
I'm a newbie in MySQL. I use it only for PHP.

I tried to upgrade myphpadmin 3.5.8.1. After doing that, I couldn't access my database and got the following error:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE db_name = 'happy2'  AND table_name = 'foto'  ORDER BY version DESC' at line 1

My Other ALL DB have error like that.

Can anyone help me?


texts: MongoDB / PHP - How to change data type for every occurrence of field in a document

texts:
I'm trying to learn how to use MongoDB with PHP. I Have built a web application that loads data from database etc. The issue I am having is that when I import my data from a .CSV using MongoDB Compass, by default everything is imported as a string.

I have also tried to do it using cmd with the following but had no luck;

mongoimport --db hddatabase --collection crimes --type csv
--columnsHaveTypes --fields "CrimeID.string(),CrimeMonth.date(2006-01),CrimeReportedIn.string(),CrimeFallsWithin.string(),CrimeLongitude.double(),CrimeLatitude.double(),CrimeLocation.string(),CrimeLSOACode.string(),CrimeLSOAName.string(), CrimeType.string(), CrimeLastOutcomeCategory.string(), CrimeContext.string()"
--file C:\Users\01\Desktop\desk\WestCrime.csv

I can see how to use the MongoDB Compass GUI to change the value of individual documents manually.. I cannot for the life of my work out how to do this for all documents in a collection. Any help for the issue would be greatly appreciated.

Update:

I tried the below based on recommendation and am getting "Could not parse type from header CrimeReporttedIn.string(); 0 documents imported

mongoimport --db hddatabase --collection crimes --type csv --fields CrimeReportedIn.string\(\),CrimeFallsWithin.string\(\),CrimeLongitude.double\(\),CrimeLatitude.double\(\),CrimeLocation.string\(\),CrimeLSOACode.string\(\),CrimeLSOAName.string\(\),CrimeType.string\(\),CrimeLastOutcomeCategory.string\(\),CrimeContext.string\(\) --columnsHaveTypes --file C:\Users\01\Desktop\desk\WestCrime.csv


texts: Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match

texts:
I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables.

CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id),
 foreign key(dept_name) references Department);

CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
foreign key (dept_name) references Department);

I had a look at Error 1239: Incorrect foreign key definition   but it didn't seem to help.


texts: How to transfer data from mysql 5.5.24 to 5.1.36

texts:
I've had a server that is not booting up, it had mysql 5.5.24. I was able to  copy the whole /data directory.

I will be temporarily using an older backup server that has mysql 5.1.36 and cannot be upgraded due to NET framework issues.

I was wondering if I could just overwrite the /data directory to have the database running?

I ask this because I usually use mysqldump to backup and restore, which as I understand backups by generating sql instructions and rebuilds the database by running such queries.

To me it seems logical to just replace the raw data and have the database working as expected. But I'm worried something might break considering im going back to an older version. Which could teoretically be unable to interpret a newly implemented way to store the raw data.

Update: The database seems to be running fine with the overwritten raw data. If anyone knows of a possible problem please let me know.


texts: I am trying to open Utility Control from View Menu, but can't find it. I am using SQL Server 2016 (SP1), Developer Edition and SSMS version 17.1. Any idea?


texts: Multiple applications accessing SQL Server

texts:
This is my first post and am very new to MS SQL Server.  We have MS SQL Server running on an existing server. We have a software application that is utilizing the SQL Server as its back-end.

We are purchasing another software application that requires MS SQL Server.  Can we utilize the same SQL Server for this new application or do we need to purchase new SQL server software and install on new server?

Both applications will use the same version of SQL Server.


texts: We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to drop these tables without mysql hanging. We have a machine which has a memory of 122GB and swap of 60 GB. We use an InnoDB Engine Hope the information suffices kindly help us with this issue


texts: How to access PostgreSQL Functions from Oracle through DBLink?

texts:
I'm having two DB server (PostgreSQL, Oracle).
I'm able to access the table in PostgreSQL from Oracle through DB-Link.
I've use the below query to access the table.

select "Cust_Id", "Mobile_No" from "schema_name"."tablename"@Dblink where "cust_id" = '123456789';

As same as I'm trying to access the function in PostgreSQL from Oracle server through DBLink. But I can't access it.
I've used below query

select "Cust_id" from "schema_name"."function_name"@DbLink('123456789');

The function will return the mobileno and cust_id in table type.

I'm getting error like.

  SQL Command not properly ended

Pls help on this issue. Thnx in advance.


texts: Is combining independent tables called normalisation?

texts:
So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc.

These are all independent data with no foreign key or any relationship whatsoever.  Some of the columns are similar, e.g. ID (obviously), datetime, name of user making the change, etc, while others are different.

Recently, I'm asked to combine all the tables into one, where the similar columns will be preserved, while the different columns will be in JSON, stored in a CLOB column.

I'm being told that this process is called "normalization" - we're converting the many tables into "normal forms".

Now, I'm not a database expert, but that doesn't seem to match what I've learned about normalization in my Intro to Database class.  Or am I just ignorant?


texts: Indexing for group by + SUM query on mysql

texts:
I have a table with the following schema:

    CREATE TABLE `wordtrend` (
      `oid` bigint(20) NOT NULL AUTO_INCREMENT,
      `monitorId` bigint(20) DEFAULT NULL,
      `nGram` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `nGramWord` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `negatives` bigint(20) DEFAULT NULL,
      `neutrals` bigint(20) DEFAULT NULL,
      `positives` bigint(20) DEFAULT NULL,
      `total` bigint(20) DEFAULT NULL,
      `trendCut` datetime DEFAULT NULL,
      PRIMARY KEY (`oid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=358539 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Is it possible to create an index in order to run the following query efficiently?

SELECT nGram, nGramWord, SUM(total) AS sTotal, SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend WHERE monitorId = 21751021 AND trendCut >= '2011-01-01 00:00:00' GROUP BY nGram, nGramWord ORDER BY sTotal DESC

We already tried the following:

KEY `RollupIndex` (`monitorId`,`trendCut`)
KEY `RollupIndex2` (`monitorId`,`nGram`,`trendCut`)

but we are getting "Using where; Using temporary; Using filesort" on the extra column.


texts: trying to execute SQL query on an oracle 10g database using java but getting ORA-00911 error

texts:
I am trying to execute a simple CREATE TABLE query on an oracle10g database using java.  This is what I am doing:

Connection conn = //create connection
//open connection.  No errors/warnings are generated so I assume this succeeds
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 VARCHAR(100), c3 INTEGER NULL, CONSTRAINT m_pk PRIMARY KEY (c1));");
...
//close connection.  No errors again so I assume it succeeds.

When I execute the command (copying and pasting into terminal window) directly on the command line, it succeeds.  When I run the above java code, this is the error I get:

java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

I dont understand how I could have an invalid character since copying and pasting it into terminal works perfectly fine.


texts: Looking for solution to add double quotes in field which has commas in SSIS

texts:
I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value should be double quoted.
Eg:

TYPE
Begin Bag 1,000 mL
Miscellaneous 20ml
Final 2,000 mL
Begin Bag 1 mL

the result should be (in the csv format exported):
TYPE
"Begin Bag 1,000 mL",
Miscellaneous 20ml,
"Final 2,000 mL",
Begin Bag 1 mL

I tried using the Derived column "\""+ TYPE +"\"" but that just adds Double quotes to every value for column TYPE.

Please help!

THanks,
Atulya


texts: Replacing Filegroup Used in Last Partition Slice for RANGE LEFT-based Partition Scheme

texts:
I have a simple date based partition function using RANGE LEFT...

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT
    FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')
GO

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT]
    TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])
GO

In the above code, filegroup [DATA_1995] would retain the values beyond the last partition boundary. So, if I elect to add new filegroups and split the function for additional boundaries in this fashion...

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1995]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1995-12-31')
GO

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1996]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1996-12-31')
GO

The filegroup [DATA_1995] remains in the last partition position for values beyond the upper-most boundary.

Can this last filegroup be changed after the partition scheme is initially created or is the only way through creation of a new partition scheme and rebuilding all indexes utilizing the old scheme into the new scheme?


texts: Image Data Type Export Different with BCP vs SSIS

texts:
I have a Stored Procedure process(scheduled SQL Job) created using BCP to export PDF file data from a image datatype to a PDF file on a network share.

Due to a client request, I'm wanting to migrate this process to SSIS.  The SSIS process works, but the pdf generated is different.  I'm trying to wrap my head around why this would be different if the source information (image field) is exactly the same?  I was wondering if it some kind of layering in the original PDF that just isn't getting reproduced using SSIS?

Thanks for any assistance with this.

Correct PDF Format

Incorrect PDF Format


texts: How to store multiple foreign key values in a single field?

texts:
I have following two tables in an MS SQL-Server Database:

user_info
  ( user_id PRIMARY KEY,
    email,
    password
  )

and

messages
  ( time_stamp,
    message,
    sender,
    recipients,
    FOREIGN KEY(sender)
      REFERENCES user_info (user_id),
    FOREIGN KEY(recipients)
      REFERENCES user_info (user_id)
  )

I am wondering if it is possible to store multiple user_id values in the recipients field of messages table. I am a newbie, please help me how to solve this problem.


texts: Converting date string to SQL DATETIME - hopefully without a udf

texts:
I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example:

Thu Oct 16 13:40:19 2014 ,Tue Apr  1 12:20:40 2014, etc.

Essentially the format is ddd MM dd HH:mm:ss yyyy.

I was wondering if there's some kind of date style that would allow me to do a convert in one line. I could write a function for this but I was wondering if there was a more elegant solution. I Googled with no luck.


texts:
With reference to RolandoMySQLDBA's answer on

MySQL: Index when joining to tables not being used (Performance optimizing question)

He mentioned

  Make sure you build an index that involves the columns deleted, visible, and date_created as follows:

ALTER TABLE blogposts ADD INDEX deleted_visible_date_created (deleted,visible,date_created);

May I ask, what is the benefit of combining columns in index? And why have you not included date_published in the index, which was also part of WHERE clause?

Should we create indexes of all columns used in WHERE clause or not?


texts: how to use index to speed up sorting in postgres

texts:
I am using postgres 9.4.

The messages has the following schema:
messages belongs to feed_id, and has posted_at, also messages can have a parent message (in case of replies).

                    Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 message_id                   | character varying(255)      | not null
 feed_id                      | integer                     |
 parent_id                    | character varying(255)      |
 posted_at                    | timestamp without time zone |
 share_count                  | integer                     |
Indexes:
    "messages_pkey" PRIMARY KEY, btree (message_id)
    "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)

I want to return all messages ordered by share_count, but for each parent_id, I only want to return one message. ie, if multiple messages have the same parent_id, then only the latest one (posted_at) is returned. The parent_id can be null, messages with null parent_id should all return.

The query I used is:

WITH filtered_messages AS (SELECT *
                           FROM messages
                           WHERE feed_id IN (7)
                           AND (posted_at >= '2015-01-01 04:00:00.000000')
                           AND (posted_at < '2015-04-28 04:00:00.000000'))
    SELECT *
    FROM (SELECT DISTINCT ON(COALESCE(parent_id, message_id)) parent_id,
                          message_id,
                          posted_at,
                          share_count
          FROM filtered_messages
          ORDER BY COALESCE(parent_id, message_id), posted_at DESC NULLS LAST
         ) messages
    ORDER BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;

Here's the http://sqlfiddle.com/#!15/588e5/1/0, in the SQL Fiddle, I have defined the schema, the exact query, and the expected result.

But the performance of the query is slow once the messages table gets big. I tried add multiple sorting indexes, but it does not seem to use the index.
Here's the explain: http://explain.depesz.com/s/Sv2

How can I create a correct index?


texts: mismatch between server schema content and exported sql rows

texts:
I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data.

I am trying to figure out, when I export my table from phpMyAdmin the exported sql file structure when opened does not match the schema seen in the admin panel. By that I mean the row sequence in the sql file are jumbled up.

I don't have any foreign key constraints set up or any other operations on the table.

Any steering and knowledge as to why this might be the case would be appreciated.