texts: Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

texts:
I'm trying to do the following query in mysql 5.5

INSERT INTO countingTable( image_count, article_id )

SELECT COUNT( article_id ) AS sum, article_id
FROM imageTable

ON DUPLICATE  KEY UPDATE image_count = VALUES(sum)

But this gives the error:

#1054 - Unknown column 'sum' in 'field list'

Edit for better explaining what i like to do:

countingTable structure:

CREATE TABLE IF NOT EXISTS `countigTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plakat` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `image_count` bigint(20) DEFAULT NULL,
  `trailer_count` bigint(20) DEFAULT NULL,
  `actor_count` bigint(20) DEFAULT NULL,
   .... (many more counting fields)
  `article_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

imageTable has the structure:

CREATE TABLE IF NOT EXISTS `imageTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `article_id` bigint(20) DEFAULT NULL,
  `image_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

(imageTable is basically a referencing table for many2many relation)

The countingTable already has rows referencing the article_id.
Now i like to count all related images and insert or update that in the
countingTable.

Later i need the same stuff for trailer_count, actor_count and so on.

If this is done the first time, the countingTable will be updated by triggers.

The idea is, to have this table so I dont't need to join all relations just for counting if they exist. (As my project needs the countings all the time)


texts:
It is one of the commonly used character sets. What does it stand for?

Is it an acronym for something?


texts: Is it possible to drop export some shema without data and the other with data

texts: I have oracle 11G R2 under RHEL 6.3 test is the name of the database, the database contain 12 custom schema, sch1,sch2,...,sch12.  is it possible to create database backup using datapump method that contain all of the schema with no data except the first three one, the data is needed.


texts: SSIS data flow gets stuck reading table with no errors

texts:
When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does nothing with no error output. I have tried waiting for 30 minutes for it to throw an error, but nothing happens (it just spins forever).

I have narrowed this issue down to the source live transactional DB on the top left side (the same process with a local copy of that live IO DB works fine). When I narrow down data selection from the live source DB to start from the row when it stopped last time, it'll then read again so many rows and then stop again.
I have checked the data in the few rows where it stopped and it's fine.

Not sure what causes this issue or how to troubleshoot this further.


texts: How to forcibly create/modify MySQL user grants, without restarting the database service

texts:
I am familiar with how to start MySQL server with the "--init-file" option, or the "--skip-grant-tables" options.

However I have a nagios based mysql monitor, which needs to query a table, and therefore I need to configure a user (GRANT SELECT ON heartbeart to 'nagios-monitor'@'123.123.123.123') for that operation with appropriate permissions granted to the nagios server host, or nrpe script host.

I'd like to have an automated installer script, to configure the table. Hence I would like to be able to carry out these operations automated, with little risk to the underlying application uptime.

It is not always clear what the mysql root user password is.  (targets are arbitrarily used developer and test boxes, and they change the passwords)
I want to be able to add monitoring without causing any downtime
Other strategies to manage users/passwords are fine, but again, I still need to apply those grants without restarting the database.

Notes

If I install the mysql from scratch, then I add a "dba" type user similar to 'debian-sys-maint' in addition to the root user, with a complex password, which sidesteps the above problem. - this is not the use-case that i am considering here.

Possible Solutions

suggestion: Spin up a second mysqld process on port 3307 (with the --init-file option to create my nagios-monitor user), pointed at the same /var/lib/mysql, but masked to only open the "mysql" database

consequences?: probably corrupt everything - could pause

sudo kill -STOP $(cat /var/run/mysqld/mysqld.pid)

and then start -

sudo kill -CONT $(cat /var/run/mysqld/mysqld.pid)

How to force the main mysql to re-read the tables from disk?

some sort of scheduled task that is run by mysqld? could append my commands to that, as its not particularly urgent to run the grants.

Multiple mysqld

http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

  Beware of the dangers of using multiple mysqld servers with the same
  data directory. Use separate data directories, unless you know what
  you are doing. Starting multiple servers with the same data directory
  does not give you extra performance in a threaded system.

http://dev.mysql.com/doc/refman/5.5/en/multiple-data-directories.html

5.3.1. Setting Up Multiple Data Directories

  Normally, you should never have two servers that update data in the same
  databases.
  Even when the preceding precautions are observed, this kind of setup works
  only with MyISAM and MERGE tables, and not with any of the other storage
  engines.

mysql> SHOW TABLE STATUS where Name = 'user'\G
*************************** 1. row ***************************
           Name: user
         **Engine: MyISAM**
        Version: 10
     Row_format: Dynamic
           Rows: 27
 Avg_row_length: 91
    Data_length: 2476
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2012-04-12 11:46:21
    Update_time: 2013-01-22 00:16:00
     Check_time: 2013-01-19 20:52:09
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

So I spun up a 2nd mysqld on port 3307, with a masked datadir like so;

# mkdir /var/lib/mysql2/
# ln -s /var/lib/mysql/mysql /var/lib/mysql2/mysql

# /usr/sbin/mysqld --basedir=/usr \
--datadir=/var/lib/mysql2 \
--plugin-dir=/usr/lib64/mysql/plugin \
--user=mysql \
--log-error=/var/lib/mysql2/server-64654.err \
--pid-file=/var/lib/mysql2/server-64654.pid \
--socket=/var/lib/mysql2/mysql.sock \
--port=3307 \
--init-file=/var/cache/chef/grants.sql \
--datadir=/var/lib/mysql2/

with a grants.sql;

GRANT ALL ON *.* TO 'nagios-user'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql --port=3307 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |
| nagios_user |

But the original mysql,doesn;t pick it up until it has been restarted! gah!

mysql --port=3306 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |


texts: MySQL Union not showing 2nd query result set

texts:
I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this.

select  *
    From
      ( SELECT  c1.ia_device_home_carrier, avg(rf1.ia_signal_strengh_rssi) as ss
            from  rf_voice_data_verizon_sec2 rf1, device_market_config d1,
                ia_device_carrier c1
            where  rf1.ia_device_id = d1.ia_device_id
              and  rf1.ia_device_id = c1.ia_device_id
              and  rf1.ia_signal_strengh_rssi BETWEEN -150.0 AND -40.0
              and  d1.ia_project_name = 'AA'
            group by  ia_device_home_carrier
         union
         select  c2.ia_device_home_carrier, avg(rf2.httpul) as http
            from  rf_voice_data_verizon_sec2 rf2, device_market_config d2,
                ia_device_carrier c2
            where  rf2.ia_device_id = d2.ia_device_id
              and  rf2.ia_device_id = c2.ia_device_id
              and  rf2.httpul > 0
              and  d2.ia_project_name = 'AA'
            group by  ia_device_home_carrier
      ) x
    group by  ia_device_home_carrier

                            +------------------------+----------+
                            | ia_device_home_carrier | ss       |
                            +------------------------+----------+
                            | xyz                    | -77.8559 |
                            | abcggg                 | -69.8158 |
                            | sfg                    | -95.8884 |
                            | VZW                    | -94.3992 |
                            +------------------------+----------+

But i need a result set like this

                            +------------------------+----------+------------+
                            | ia_device_home_carrier | ss       |   httpul   |
                            +------------------------+----------+------------+
                            | xyz                    | -77.8559 |  7848.5175 |
                            | abcggg                 | -69.8158 |  5623.5231 |
                            | sfg                    | -95.8884 |  7228.5677 |
                            | VZW                    | -94.3992 |  7118.3175 |
                            +------------------------+----------+------------+


texts: In my case wherein I have a file file_01.mdf and file_02.ndf under 1 filegroup, if ever file_01.mdf was already full (no autogrowth enabled), will it give me error on file_01.mdf if data is attempted to be added in it?


texts:
Let's say I got word transcriptions (space separated) in one column. Furthermore I got a set of legal phones.

How can I split every transcription @ a whitespace to check whether all used  phones are legal ones?

Main question is: how to get a distinct on a column split @ a whitespace?

Let's say the data looks like this:

ID | transcriptions
 1 | th i s
 2 | i s
 3 | s o m e
 4 | t e st
 5 | d a t a

Now the output should look like the following:

  th
  i
  s
  o
  m
  e
  t
  st
  d
  a


texts: What is the relation between MySQL pages and Operating system pages?

texts:
By MySQL pages i mean this :

https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

and operating system pages :

https://en.wikipedia.org/wiki/Page_(computer_memory)

I understand both of them, but i don't understand their relation, so DBMS is basically a program that runs on top of our O.S correct? so why are DBMSes like MySQL don't just use the page size of the operating system and instead let the DBA decide what should the page size be?

what happens when the O.S page size is 8KB but DBMS is 16KB? and what if its vice versa?

Does a difference between these two affect performance or anything else?


texts: How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint

texts:
I have an instance of SQL Server 2012 running on Windows 2008R2 along side another instance of SQL Server 2012 that is running SharePoint (I think it is SharePoint 2010).

When I try to access the http://localhost/Reports_<InstanceName> I am getting:

A certificate warning (which I proceed through - I don't really care about this - its a dev machine)
The following text: Cannot connect to the configuration database.

Google says that that error message is SharePoint related so I am inclined to think that SharePoint is highjacking the SSRS request.

Is there a configuration I can make to SSRS or something else (IIS maybe??) that will let me access the Report Server?

Note: I can connect to Reporting Services through SSMS just fine.


texts: Generic data type that belongs to "something", where the same data type/shape can belong to multiple tables

texts:
In postgres one often has one-to-many tables, where multiple entities in table B refer to one element in table A.
But can this also be used the generalize data shape, where I have a generic shape of data store in table B, but it can either belong to an entity in table A or table C? Is there a clean way to do this without DRY nor with redundant "empty" fields?
Say I have a class "time period" - which would include an id, start_date, end_date. Now I have two tables A and C where each entry in those table both have time periods, for the rest there's little in common between those tables.
The earlier solution I would make is a time_period_a and a time_period_b table to copy the format of the time period. But this violates DRY, and I've experience lots of troubles with this violation (say in one table I forgot to update the dates to now include timezone).
I've also thought about adding both columns for a and b, and setting them to NULL if not needed. But this creates a lot of trouble where I have to add either some triggers or complexity to prevent both be NULL or both be not NULL.
What is the canonical solution for this? Is there a way to store -in the field, not in the column- that the field refers to a specific table id? Instead of just a generic number and then get from the logic of the column/foreignkeyconstraint which table it refers to?
Another solution I could see is that one defines the datashap separate from the tables, and then say "this table has datashape xyz with additional a foreign key to table A" and another table with "this table has datashape xyz with additional a foreign key to table B".


texts:
I have CentOS 64bit with CPanel installed and I use

service mysql stop

However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? Right now I have to do killall -9 mysql but is there a better way?

The server is also very very active.


texts: Is there an Oracle function to check for valid Japanese characters (Non-ASCII)?

texts:
Is there an Oracle function that can recognize if a character is readable or not?

I would like to search a specific column for garbled characters. I know that this can typically be done with a regular expression. However, my database contains Japanese characters, so it is not just a simple matter of an [A-Z][0-9] pattern.


texts: What type of rows does `SELECT my_table FROM my_table` return?

texts:
I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges):

SELECT my_table
FROM my_table;

It seems to turn a one-column result, with multiple rows.

What would be the name of the type of each row returned? A row value of my_table?

(I tried reading the documentation (sql-expressions) but, I could not find a specific answer.)


texts: Is there any such thing as an https storage engine?

texts:
Maybe I have too much time on my hands, and this may be a stupid idea, but...

I have recently had to download data in table-form from websites, and I got to thinking that it might have been easier, if I could define these "https://..." as tables/views that I could simply query with SELECT. Is this simply stupid?


texts: What's the limit of TDE certificates allowed in master database [SQL Server]

texts:
Let's say I have a SQL instance with 10k databases that I want to enable TDE using different certificates for each database.
Is there any kind of limit to creating certificates on master database?
Will I be allowed to create 10k certificates or more?

Any help is appreciated. Thanks


texts: How to create a backup schema script including System Tables in Sql Server 2008 R2?

texts: I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I need to do something more to map this table to the scripts?


texts: What will be the best suited datatype to store a text file in database

texts: I want to store a text file containing logs in a database(SQL Server). That file size can vary from 1 MB to 10 MB. what will best suited data type to store it in to the database. Will it be BLOB or FILESTREAM or any other type?


texts: Better way to add extra attributes in RBAC (Role Based Access Control) tables?

texts:
I have implemented RBAC (Role Based Access Control) tables. I have a requirement to add extra attribute on some roles, like 2nd_pwd attribute in Admin role, address attribute in Customer role.

I have done this design, but it violates data integrity. When you removed (let's say) Admin role, you also need to remove admin data from Admin table. So I decided to add trigger for this job.

Question:

Is there a design for this requirement without trigger but still doesn't violate data integrity?
What is the common implementation for this requirement? Is it normalized enough?

]1

Thank you.


texts: PostgreSQL to MySQL migration to use database for WordPress site - how to's?

texts:
I have a complex issue to solve, related to database migration. I have a website full of news to migrate that is currently based on a PostgreSQL database and written in PHP, without a CMS. I have a dump of the database that is a huge text .sql file of 300.000+ lines (finished completely with success); the new version of the website should be based on WordPress.

Could you please kindly suggest some best practices of migrating this database dump properly to a MySQL database and importing it afterwards the right way into WordPress, e.g. through PHPMyAdmin? What procedures are to follow so that the database works with the WordPress install so that one can start with the front-end part.

Thank you very much in advance for your help!


texts: Explain plan shows a table not in the query

texts:
In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. It's on line #8 in the explain plan below. Can anyone tell me why this is and where to go from here?

Query:

Select distinct kb.FSSBD, kb.SSSBD, ca.AUFNR, ca.PLNBEZ, kt.maktx,ca.GAMNG,ca.GMEIN
From
    SAPR3.CRHD cr,
    SAPR3.KBED kb,
    SAPR3.CAUFV ca,
    SAPR3.MAKT kt
Where cr.MANDT = '100'
And cr.OBJTY = 'A'
And cr.WERKS = '6802'
And cr.STAND = 'MPL06'
And cr.LVORM = ' '
And cr.MANDT = kb.MANDT
And cr.KAPID = kb.KAPID
And kb.TYPKZ in ('1', '7')
And kb.PLNUM = ' '
And kb.MANDT = ca.MANDT
And kb.BEDID = ca.BEDID
And ca.LOEKZ = ' '
And kb.FSTAD <= to_char(sysdate+10, 'YYYYMMDD') And kb.SENDD >= to_char(sysdate-10, 'YYYYMMDD')
And kb.BSTKZ = '3'
And kt.mandt=ca.mandt
And kt.matnr=ca.PLNBEZ
And kt.SPRAS='E'
ORDER BY kb.FSSBD

Explain Plan:

SELECT STATEMENT  ALL_ROWSCost: 17,700  Bytes: 214  Cardinality: 1
    18 SORT UNIQUE  Cost: 17,698  Bytes: 214  Cardinality: 1
        17 NESTED LOOPS
            15 NESTED LOOPS  Cost: 17,697  Bytes: 214  Cardinality: 1
                13 NESTED LOOPS  Cost: 17,697  Bytes: 156  Cardinality: 1
                    10 MERGE JOIN CARTESIAN  Cost: 11,655  Bytes: 1,705,622  Cardinality: 15,094
                        6 NESTED LOOPS
                            4 NESTED LOOPS  Cost: 25  Bytes: 94  Cardinality: 1
                                2 TABLE ACCESS BY INDEX ROWID TABLE SAPR3.CRHD Cost: 2  Bytes: 25  Cardinality: 1
                                    1 INDEX RANGE SCAN INDEX SAPR3.CRHD~E Cost: 0  Cardinality: 38
                                3 INDEX RANGE SCAN INDEX SAPR3.KBED~1 Cost: 0  Cardinality: 178
                            5 TABLE ACCESS BY INDEX ROWID TABLE SAPR3.KBED Cost: 23  Bytes: 69  Cardinality: 1
                        9 BUFFER SORT  Cost: 11,632  Bytes: 13,381,586  Cardinality: 704,294
                            8 TABLE ACCESS BY INDEX ROWID TABLE SAPR3.AUFK Cost: 11,630  Bytes: 13,381,586  Cardinality: 704,294
                                7 INDEX RANGE SCAN INDEX SAPR3.AUFK~D Cost: 566  Cardinality: 807,616
                    12 TABLE ACCESS BY INDEX ROWID TABLE SAPR3.AFKO Cost: 0  Bytes: 43  Cardinality: 1
                        11 INDEX UNIQUE SCAN INDEX (UNIQUE) SAPR3.AFKO~0 Cost: 0  Cardinality: 1
                14 INDEX UNIQUE SCAN INDEX (UNIQUE) SAPR3.MAKT~0 Cost: 0  Cardinality: 1
            16 TABLE ACCESS BY INDEX ROWID TABLE SAPR3.MAKT Cost: 0  Bytes: 58  Cardinality: 1


texts: Is there a way to avoid duplicating the definition of a foreign key?

texts:
I was wondering about this example

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

The definition of city as varchar(80)  is duplicated here. Is there a PostgreSQL syntax that allows not to duplicate varchar(80), only basing itself on references cities(city) so as to create the weather table?


texts: Analysis Services Project: Data Source per Enviroment

texts:
I'm trying to configure a multi dimensional analysis service project, so that each developer of a team can configure his/her own data source (oracle).

Changing the deployment target is easy, but how do I specify which data source the project should use per configuration?


texts: Seeding new data daily in an ever changing database

texts:
I've been working with a data team that imports data from suppliers daily into a MySQL DB. The data from the supplier needs to be worked on by them and formatted properly before we can use it in production.
Every night, I must import that "formatted" data into a production Amazon RDS instance (same MySQL version). Here's the catch. The databases are for an e-commerce solution. Products might be added everyday by the data team, but new products could have been added by sale staff via the site directly into the production database which explains why the 2 databases are desynced constantly.
This is what I did to solve the issue :

Each element in both database gets a UUID (same across both DBs) assigned so that we can identify it uniquely when importing and set its foreign keys properly to references we already have in the database with the same UUID.

example: the data team adds a product in the "products" table. this product
might have the id (3) and the category_id (23). When we import it, we
might already have 100 products created by our staff in that table so we must assign this new
product the id (101). Also, if we already have it's category created on our end,
it might not be the id (23) so we identify the relation on their end,
look for the category with the same UUID on our end (let say it's category
(53)) we then assign the category_id (53) to the product.

If you need to visualize it :
What the data team wants to import :

Products
-----------------------------
|  id  | uuid  | category_id |
-----------------------------
|   3  | 12345 |     23      |
------------------------------

Categories
-----------------
|  id  | uuid   |
-----------------
|  23  | 518149 |
-----------------

What we already have :

Products
---------------------------------------
|  id  | uuid  | category_id          |
---------------------------------------
|  100 | ...   |     ...              |
| we want to insert new product here  |
---------------------------------------

Categories
-----------------
|  id  | uuid   |
-----------------
|  53  | 518149 |
-----------------

What the script does :

What is our next available product id? 101.
The new product category_id is 23. If I look at that relation on their end, it gives me a category with UUID : 518149. Do I already have that UUID in categories on my end? Yes. What is it's id? 53.
Assign the new product ID 101 and category_id 53

Imagine this for all tables and all relations. This is all done via a script in our PHP Laravel API that runs nightly.
Sorry for the long backstory as this issue is not easily explained by text. Now what I am wondering is how bad is it to handle the data in such a way. It seems to be VERY primitive to change the keys like this. This manipulation is preventing me from using nice automated tools like Amazon DMS or AWS Glue which could be used for nightly imports. I'm not used to work with an external data team that have a copy of our DB structure and work in it 7 days a week for us to import it into our ever moving database but I am sure this is not a rare scenario so there must be something obvious I am missing.

How could I handle those nightly seeds better?
Am I missing out on some tool that should obviously be used here but that we are trying to code on our end for no reason?
Am I just overthinking something that is just "normal" to have to do when managing the production environnment with another team seeding data and should just make my script very robust and ignore trying to automate it more by fear of causing issues more than solving them.


texts:
How can I do this MySQL statement in an IBM Informix?

select type, channel, teilnr, starttime, endtime, usedtime, host
from online_time
where starttime < DATE_SUB(NOW(),INTERVAL 15 MINUTE)
order by starttime desc

I tried this one in Informix, but it gave a syntax error.

select type, channel, teilnr, starttime, endtime, usedtime, host
from online_time
where starttime < subdate(CURRENT, INTERVAL "15" MINUTES)
order by starttime desc


texts: MySQL uses intersection instead compound index

texts:
I have compound index on following columns:

(country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count)

When I run following query:

SELECT COUNT(*)
FROM `user_profile`
WHERE (`user_profile`.`lazy` = 0 AND
      `user_profile`.`has_avatar` = 1 AND
      `user_profile`.`inactive` = 1 AND
       NOT (`user_profile`.`id` = 3247028 ) AND
      `user_profile`.`country_id` = 374 AND
      `user_profile`.`challenge_count` < 10 AND
      `user_profile`.`age` BETWEEN 18 and 28 AND
      `user_profile`.`gender` = 'F' )

It is used correctly. However, when I run following query it is not used.

The WHERE parameters are the same. In this case mysql uses intersection of separate indexes:

SELECT *
FROM `user_profile`
INNER JOIN `iqApp_basecountry`
ON ( `user_profile`.`country_id` = `iqApp_basecountry`.`id` )
INNER JOIN `auth_user` ON ( `user_profile`.`user_id` = `auth_user`.`id` )
LEFT OUTER JOIN `iqApp_relationshipstatus`
ON ( `user_profile`.`relationship_status_id` = `iqApp_relationshipstatus`.`id` )
LEFT OUTER JOIN `iqApp_workstatus`
ON ( `user_profile`.`work_status_id` = `iqApp_workstatus`.`id` )
LEFT OUTER JOIN `iqApp_fieldofwork`
ON ( `user_profile`.`field_of_work_id` = `iqApp_fieldofwork`.`id` )
LEFT OUTER JOIN `iqApp_fieldofstudy`
ON ( `user_profile`.`field_of_study_id` = `iqApp_fieldofstudy`.`id` )
LEFT OUTER JOIN `iqApp_educationlevel`
ON ( `user_profile`.`education_level_id` = `iqApp_educationlevel`.`id` )
LEFT OUTER JOIN `iqApp_religion` ON ( `user_profile`.`religion_id` = `iqApp_religion`.`id` )
WHERE (`user_profile`.`lazy` = 0 AND
     `user_profile`.`has_avatar` = 1 AND
     `user_profile`.`inactive` = 1 AND NOT
      (`user_profile`.`id` = 3247028 ) AND
     `user_profile`.`country_id` = 374 AND
     `user_profile`.`challenge_count` < 10 AND
     `user_profile`.`age` BETWEEN 18 and 28 AND
     `user_profile`.`gender` = 'F' )
  LIMIT 1

Here is explain:

SELECT_TYPE: SIMPLE
Table: user_profile
Type: index_merge

POSSIBLE_KEYS: PRIMARY,user_id,user_profile_d860be3c,age,gender,challenge_count,lazy,has_avatar,inactive,country_id,country_id_2

KEY
user_profile_d860be3c,lazy,has_avatar,inactive,gender

KEY_LEN
5,1,1,1,5

REF
None

ROWS
35394

EXTRA
Using intersect(user_profile_d860be3c,lazy,has_avatar,inactive,gender); Using where


texts: Best way to delete large set of rows knowing field to NOT delete

texts:
Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies.  I'm trying to delete all the rows except the ones belonging to the 4, and I know the way I have it is not optimal.

DELETE FROM appointments
WHERE (appointments.company_id NOT IN (6, 753, 785, 1611))

another example is when I have to delete records on a table where the company_id is on an associated table:

DELETE FROM mappings
WHERE mappings.id IN (SELECT mappings.id
                      FROM code_mappings
                      INNER JOIN codes ON codes.remote_id = mappings.code_remote_id
                      WHERE (codes.company_id NOT IN (6, 753, 785, 1611)))


texts:
I have data structured as follows:

create table T
( field_1 char(1) not null
, field_2 int not null
,    primary key(field_1, field_2)
);

insert into T (field_1, field_2)
values ('A',1)
      ,('A',2)
      ,('B',3)
      ,('C',3)
      ,('D',4)
      ,('E',4)
      ,('E',5)
      ,('F',6)
      ,('G',1);

I would like to obtain sets that have either field_1 or field_2 in common, like this:

field_1     | field_2   | set
-------------------------------
A           | 1         | one
A           | 2         | one
G           | 1         | one
-------------------------------
B           | 3         | two
C           | 3         | two
-------------------------------
D           | 4         | three
E           | 4         | three
E           | 5         | three
-------------------------------
F           | 6         | four

Basically, rows having common values in field_1 OR field_2 should be assigned to the same ID. I feel like this is a very basic operation but for some reason I cannot wrap my head around the proper way to do it.

Any clues?


texts: Mysql reliable with 1000 new entries / minute?

texts:
I have been developing an application that in the WORST case writes 1000 entries each minute into a database for over a year...

I wanted to use Mysql as DB, but I have read that with high datatransfers it becomes unreliable when writing. Is this true? Is 1000 entries considered a high amount of data? What would be such a high amount of data? Would corrupt data mean that I miss one entry or that I lose the whole table?

Thanks


texts:
I'm trying to join 2 different tables (Table99 and Table2) to Table1.  The first is a straight-forward RIGHT JOIN.  The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2, and bring back results from Table1 that don't exist in Table2

Example:

 FROM Table1 tbl1
      RIGHT JOIN Table99 tbl99 ON tbl1.CARD_ID = tbl99.CARD_ID
            AND tbl1.DATE_OPEN = tbl99.DATE_OPEN

      LEFT JOIN Table2 tbl2 ON tbl1.ACCOUNT_NUMBER = tbl2.ACCOUNT_NUMBER
           AND tbl1.APPL_ID = tbl2.APPL_ID'

WHERE tbl1.ACCOUNT_NUMBER <> tbl2.ACCOUNT_NUMBER

The Left Join clause is where I'm trying to pull in all of the results from tble1 that do NOT exist in tbl2


texts: What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"?

texts:
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:

Two Important Differences Between SQL Server and PostgreSQL

The first point (“CTEs are optimization fences”) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).

However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc.  For example:

A Better Way To Select Star

So, it seems like SQL Server “honors” the CTE as an optimization fence SOMETIMES.  Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?


texts: Does a single replica gives any performance improvement?

texts:
Consider a case where you have a single PostgreSQL database. Then you add a replica (hot standby) and you send all the reads to that replica. This is what does Rails for example.
Does this gives any performance benefit? For my understanding the answer is no.
Let me explain:

when you have only a single database it gets N writes and M reads
when you add the replica, the replica has to perform N writes (replicate them) and M reads... so there isn't any advantage.

Basically adding replicas does not add any benefit for writes (i.e. 2 x number of machines => 2 x number of writes).
The only advantage would be to split the reads: but in that case you need to split the reads between the master and the replica (or between multiple replicas), otherwise it is useless. So the strategy adopted by Rails (i.e. moving all reads to the single replica) seems not effective to me.
Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?


texts: How to retrieve Oracle Key Performance Indicators in 5 minute intervals within a time range

texts:
I have Oracle 11g attached to a Performance Test environment. A performance test runs for two hours. After the run I want to programmatically extract a variety of in key indicators from the database to store with the results.

What commands would I run before and after the test to collect key indicators, ideally grouped in 5 minute totals.

CPU
Memory
Total SQL requests
Logical IO
Physical IO

And bottleneck indicators, (various waits for locks and latches).

I will accept a handy link to an article or a collection of useful SQL commands to explore.


texts: How to keep archive table accessible for some users which is not frequently accessible?

texts:
I have partitioned my table based on some boundary ranges.

My Database is built at MSSQL server 2008 R2.

I have done partitioning for a table which has 40 million records in it. Now it's time to move some historical data from my production table into Archive table and I have planned to keep the historical data at my Archive table for some period of time (2 months) because unfortunately some clients may ask for the old data access. So, the production table will be frequently accessed by users where as the Archive table is going to be accessed less frequent.

Finally I got confused that how to keep both production table and Archive table accessible in an efficient manner ?

I have a OLAP database which fetches data from the Production table but after moving some data to Archive table, how can I manage that or how to retrieve from both the production and Archive table ?

Is there any good plan for my issue ?

Kindly advice me on that.


texts: Does a MySQL dump that contains triggers double data on import?

texts:
I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take  a mysqldump and import it to another server. So when we take mysqldump, it will be packed with triggers by default as well.

So if I import that dump with triggers to another server, do those triggers fire during the import and create duplicate data?

I know we can take dump without triggers, but I am asking if I import dump with triggers does this create data duplication?


texts:
In my syntax why does the NOT IN statement not exclude NULL values?  To me, if NULL does not exist in #GoodAnswers it should return the dv

Below is syntax, can someone help explain this to me?

Create Table #GoodAnswer
(
  answer1 varchar(100)
  ,dv varchar(10)
)

Insert Into #GoodAnswer Values
('Blue','Yes'),
('Pink','No'),
('Purple','No')

Create Table #Data
(
  bbc varchar(100)
  ,answer varchar(100)
)

Insert Into #Data Values ('lal', NULL), ('raj', 'Orange'), ('enif', 'Zoo')

Select bbc
    , case
        when answer not in (
            Select answer1
            from #GoodAnswer
        ) then (
            Select answer1
            from #GoodAnswer
            where dv = 'Yes'
        )
        else answer
        end as [Valid Answer]
FROM #Data

Drop table #GoodAnswer
Drop Table #Data

My data returned from this result-set is

+------+--------------+
| BBC  | VALID ANSWER |
+------+--------------+
| lal  | NULL         |
| raj  | Blue         |
| enif | Blue         |
+------+--------------+

I feel that instead of NULL being returned it should return Blue since that is the dv in the table


texts: Why my sql query run faster than my stored procedure?

texts:
I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables.
If i use temporary table the time of execution : 6 second but my stored procedure 370 second.

I really need help.

      CREATE DEFINER=`datasll`@`...` PROCEDURE `product`()
      BEGIN
      EXPLAIN SELECT
             cs.id,
             cs.fk_c,
             cs.so,
             cs.pr,
             cs.date,
             cs.o_p,
             cs.sh_c,
             cs.x,
             LEFT(cs.x,2),
             cs.doc,
             cs.fk_op,
             cc.id_c_c,
             cc.name,
             cc.descrip,
             left(cc.n_o,3) as bon,
             cc.fk_c_br,
             cc.de,
             cc.m_mat,
             cc.color,
             cc.fk_c_s
      FROM
            data_pro.catas cs

      LEFT OUTER JOIN
            data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
      WHERE
            data_pro.cc.pap = 1
      AND   data_pro.cc.stati = 'b';
      END

When i use EXPLAIN this's the result :

Many Thanks for any help.


texts: Select records that do not have associations outside a certain list

texts:
I have a mySQL table of nodes and each node can be associated with many categories.  I have a list of category ids that are acceptable (498, 499).  I want to select only nodes who do not have categories that are not in the list of acceptable category ids.

In this example, I should get Toby and Joseph back, but not Susan...

I was initially trying this, but the IN clause does not exclude other categories:

SELECT n.nid, n.title, category.dir_phys_category_tid
FROM node AS n
INNER JOIN field_data_dir_phys_category   AS category   ON n.nid = category.entity_id
WHERE n.type = 'dir_physicians'
 AND n.`status` = 1
 AND category.dir_phys_category_tid IN ( 498,499 )

Answer: modified from RolandoMySQLDBA's answer below, with formatting & naming changes

SELECT desired_categories.*
FROM
    ( SELECT nid, title, COUNT(1) AS all_categories
        FROM node
        INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
        INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid IN (498,499)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

WHERE all_categories = good_categories;


texts: How to restore MySQL database files without having a MySQL instance?

texts:
After a crash of my PC I'm left with several databases (27Gb in total) that are left on my data drive, while my OS drive (Windows 7) is gone.
I've reinstalled the machine by now (Windows 10) and set up a new MySQL server (via installation of XAMPP). I would like to recover the old databases and add them to the new MySQL server.
What would be the best way to copy/move/restore these databases to the new installation?
The old MySQL install was version 5, but I have no idea what specific version. I could set up a new MySQL install version 5 if needed, but I'd prefer a solution that works regardless of version (within reasonable limits, of course).
I've browsed through two questions that are similar: How to restore MySQL database from Files and Can I restore a MySQL table from a file (non-sql) backup? but they don't address the possible difference in version.
Thanks!


texts: Why recovery took longer time even though analysis phase is not that long

texts:
We are doing Volume Snapshot restoration from Production to Warm Stand-By Server on daily basis. Daily it takes around 10 mins for DB to recover. But yesterday it took 44 mins to recover.

I am confused on where exactly the time was spent (analysis only took ~12.5 minutes) and once I identify that I want to know why it happened and how can we fix it.

Message

  Recovery completed for database XXXXXXX (database ID 7) in 2612
  second(s) (analysis 748452 ms, redo 0 ms, undo 814 ms.) This is an
  informational message only. No user action is required.

Note: There are only 53 VLFs in the log file, ranging in size from 6 MB to 244 MB (auto growth setting for the log file is 10%).

Total DB Size: 4598 MB
Log File Size: 2698 MB

Version info:

  Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) Jul 21 2019 21:16:47 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)


texts: How to delete all but the latest entry per customer in the fastest way?

texts:
I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as decimal number. customerId is indexed.

Now I want to find the solution to delete all but the latest row per customer that has the best performance. The thing is, this table will be constantly filled and the cleanup should be done once a day.

What I have tried:

Use a cursor of "select customerId from customers" and iterate over it. Fetch the max creation time per customer. Delete all entries with the id and with a different creation time. -> Sadly, this is incredibly slow.
"Smart" DELETE queries with subselects aren't possible, because MySQL does not allow calling select in a subquery.
The client does not want to user triggers.

Any other ideas?


texts: Archiving SQL Server data on disk to be pottentially restored

texts:
Right now, we are backing up data per table into text/CSV files per month.  This works fine for fairly small tables.  For bigtables, the text files become so big that the restore process for a month of a table can take days to restore.

We are looking for a way that we can partition the data per month and store it somewhere that if it is ever needed it can be restored for a single month or all months required by the customer... the problem is that we have to find a solution that will work for SQL Server 2008 to SQL 2017 for Express to Enterprise editions.  Unfortunately, 99% of the databases that our customers have installed are using the simple recovery model.

I have looked into partioning with different filegroups, but couldn't dettach a filegroup (a month worth of data) and reattach it by itself.
I've also looked into BCP, but the performance isn't that great.

It would be great if I can get the partitioning with different filegroups to work since it seems the closest thing to what we want to accomplish...

I'm exploring the SQLPackage.exe which I just came across in one of the posts (had never heard of it before)

I'm looking for other ideas I can explore?

I've researched posts on stackExchange but couldn't find a solution to my problem.


texts: Coping MySQL DB between two servers: which server shoud initiate the operation?

texts:
I want to copy a database from serverA to serverB. The servers can communicate directly with each other. So I'm able to do something like:

ServerA is sending the database to ServerB:

dbadm1@serverA:~$ mysqldump -C ... database | mysql -h serverB ... database

or: ServerB is ,,draining'' the database from ServerA:

dbadm2@serverB:~$ mysqldump -C -h serverA ... database | mysql ... database

The result of both commands will be, of course, the same.

But which one is better/faster/etc.? Or are they the same?
Initiate coping from serverA or from serverB?


texts: Remove parent and child rows if child or parent fits criteria

texts:
I have the following table:

OrderID | OldOrderID    | Action    | EntryDate | Source
    1   |     NULL      |   Insert  | 2016-01-12|   A
    1   |     NULL      |   Remove  | 2016-01-13|   A
    2   |     NULL      |   Insert  | 2016-01-12|   B
    3   |     NULL      |   Insert  | 2016-01-12|   C
    4   |       3       |   Insert  | 2016-01-13|   C
    4   |     NULL      |   Remove  | 2016-01-14|   C

I'm trying to query all rows that dont have the Action Remove. A row can also have a child (a child can also have a child). If a child get the Action Remove it should also remove the parent and so on until it gets to the first parent.

The query I'm currently using fails to remove the parent if a child is removed.

WITH Active AS
(
  SELECT *, rn = ROW_NUMBER()
    OVER (PARTITION BY OrderID,Source ORDER BY EntryDate DESC)
  FROM Orders
)
SELECT *
FROM Active WHERE [Action] <> 'Remove' AND rn = 1;

One other problem is that OldOrderID can be 0 or equal to OrderID insted of NULL.

Can this problem be solved with SQL only or should I programaticly filter a simple query?

EDIT:
As I might have explained it badly some extra information:

Each row can make a child of its self ( New OrderID, OldOrderID = prevoius OrderID). Or make a "clone" of its self with a diffrent Action, but the same OrderID.

A child row will be inserted with a  OrderID and an OldOrderID that refrences a OrderID from a prevoius row. If a child is updated to indicate it has been removed it will get a new row with the same OrderID, but the OldOrderID might be NULL or it might still contain a value indicating wich row is its parent.

The Source column idicates where the data came from as this data is aggreggated in a single table from 5 difrent sources. The same OrderID number can exitst on multiple sources, but it dosent mean its the same order. Each row also has a Uniqe ID, but I didnt include it in the table as it didn't seem useful.

The exception are rows that get fake children. They have OldOrderID equal to 0 or is the same as OrderID - in this case its the same as if OldOrderID was equal to NULL.

In the example table I only need the row with the OrderID 2. OrderID 1 gets a "clone" and is removed. OrderID 3 gets a child with OrderID 4. This child then gets removed. So the parent, 3, should also get removed.

I hope this clears some of the questions.


texts:
We have a huge database on work, and i think the combinasion of bad hardware and bad indexing are the reason for our backend are not working so perfect as we work on its shut do.

So are there are perfect or great way to work with index and query optimizion in MySQL and not only use the slow-log and test every single query out?

hope there are way around so we can find all the bad querys and fix them.


texts: Privileges to select any table in Oracle

texts:
I'm using a Oracle database client application that connects to 1521 port to do "select" commands over every table in a Oracle db 12.1.
I told dba to create this role with these commands:
create role G_CLASSIFIER
grant CONNECT to G_CLASSIFIER
grant select any table to G_CLASSIFIER
grant select any dictionary to G_CLASSIFIER

After that, i told dba to create a new user with that role:
GRANT g_classifier to dbuser

However, "dbuser" doesn't access some schemas with those privileges. What i don't know if this is a privileges issue or not.
Should "dbuser" be able to access every table of every schema in the database or it depends of some db configuration? Am i missing something?
If the user should have access to every table and schema, this is not a db issue.
I know nothing about database administration nor that Oracle database so i will be very grateful if you could help me with this Oracle dubt.


texts:
SELECT gs.h hours, severity."alarmSeverity", COUNT(t1.eventtime)
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT t1."alarmSeverity"
                FROM (SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3)t1
                      WHERE t1."alarmSeverity" != 0) AS severity
     LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h AND t1."alarmSeverity" = severity."alarmSeverity"
     AND t1.eventdate+t1.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z'
     GROUP BY gs.h, severity."alarmSeverity"
     ORDER BY gs.h, severity."alarmSeverity";

this is supposed to give a list of hours from 0-23 along with the count of alarms triggered grouped by severity like

    {
        "hours": 0,
        "alarmSeverity": 1,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 2,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 3,
        "count": "0"
    },...

the error is

Query 1 ERROR: ERROR:  syntax error at or near "ON"
LINE 14:      LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h...


texts: How to generate unique and sequential Id values

texts:
We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it:
CREATE TABLE `Task` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  CONSTRAINT `task_vendor_id_foreign` FOREIGN KEY (`vendor_Id`)
  REFERENCES `Vendor` (`vendor_id`))
  ENGINE=InnoDB AUTO_INCREMENT=1
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We have multiple Vendors, and we want that the id values should be sequential starting from 1 for each Vendor.
Example:

Vendor 1: ids: 1, 2, 3

Vendor 2: ids : 1, 2, 3

One of the solutions that clicks me is to, whenever a Task is added, we find out the max(id) for that vendor and then increment it by 1 and assign it to the corresponding row, e.g.:
INSERT INTO `Task` (`id`, `vendor_Id`, `name)
VALUES ((select max(id) + 1 from Task where vendor_Id = 1), '1', 'Test');

But I get an error message with that approach:

You can't specify target table 'Task' for update in FROM clause

From MySQL docs:

You cannot update a table and select from the same table in a subquery.

Questions

How do I go about solving the problem with this approach?

Are there other options?


texts: Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801

texts:
We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table.

The table doesn't have clustered index, but it does have a "PK_ID" column, which is bigint and autoincrement.

Last week, we deleted roughly 80 million from a total of 90 million records in that "login table" (with a delete from..., we didn't truncate the table).

But since then we see multiple cases where the logon trigger fails with

Logon failed for login 'SQL\xy' due to trigger execution.
Changed database context to ‘master’.
(Microsoft SQL Server, Error: 17892)

We did restart the whole windows server a couple of times and afterwards the error went away for a few hours, but seems to come back every now and then. Note that this only started to appear after we deleted so many records.

We found out that prior to users not being able to login, we get the following error:

Msg 2801, Level 16, State 1,:
The definition of object 'my_proc' has changed since it was compiled.

Afterwards we get the error 17892 as described above.

Does anybody know what the problem could be?

Trigger definition

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Tr_My_Logon]
     ON ALL SERVER  WITH EXECUTE AS 'xy'
     FOR LOGON
     AS
     BEGIN
        BEGIN TRY
            INSERT INTO mydatabase.myschema.myLogonHistoryTable
                SELECT GETDATE(),
                      ORIGINAL_LOGIN(),
                      EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(500)'),
                      @@SPID,
                      program_name(),
                      HOST_NAME(),
                      EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
       END TRY

       BEGIN CATCH
           PRINT ERROR_MESSAGE()
       END CATCH

     END
    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO


texts:
I have a table whose schema along with data (table_name : raw_data) appears to be this :

name | category | clear_date |

A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | GOOD     | 2020-05-30 |
A    | BAD      | 2020-05-30 |
A    | BAD      | 2020-05-30 |

Now if I perform a "groupby" operation using the following statement :

 SELECT name, category, date(clear_date), count(clear_date)
 FROM raw_data
 GROUP BY name, category, date(clear_date)
 ORDER BY name

I get the following answer :

  name | caetgory | date      | count |
  A    | GOOD     |2020-05-30 |  4    |
  A    | BAD      |2020-05-30 |  1    |
  A    | BAD      |2020-05-31 |  1    |

IN order to produce the pivot in following format :

 name | category | 2020-05-30 | 2020-05-31 |
 A    | GOOD     | 4          |   NULL     |
 A    | BAD      | 1          |   1        |

I am using the following query :

 select * from crosstab (
    'select name, category, date(clear_date), count(clear_date) from raw_data group by name, category, date(clear_date) order by 1,2,3',
    'select distinct date(clear_date) from raw_data order by 1'
    )
    as newtable (
    node_name varchar, alarm_name varchar, "2020-05-30" integer, "2020-05-31" integer
)
ORDER BY name

But I am getting results as follows :

    name | category | 2020-05-30 | 2020-05-31 |
    A    | BAD      |    4       | 1          |

Can anyone please try to suggest how can i achieve the result mentioned above. It appears crosstab removes the duplicate entry of A automatically.


texts:
Do we have to Install stand-alone instances of SQL Server 2012 or Failover-Cluster SQL Server Instances while configuring Always-On?

I am asking this question because I saw few configurations using stand alone instead of Clustered SQL Server Instances.

And when we say:

  Always-On will support up to two secondary replicas synchronously replicated per availability group with Automatic Failover.

Does it mean:

  A Database configured for Always-On HA/DR can be in synchronous mode and can be failed over to Two replicas(Two other SQL Server Instances)

Maybe I am getting confused but can you please explain about Always-On supporting two secondary replicas synchronously with Automatic Failover?


texts: How to shrink big database files faster than dbcc command?

texts:
There is a DBCC command to shrink files of a database. Unless the files are almost empty or the free space is nearly unfragmented this command is often refered as 'don't use'. In rare cases I need to shrink files to a new size. An alternative to the DBCC command in this szenario is what I am looking for.

The databases I'm working on are 'big data': Size of each DB some TB, Size of each file some 100GB.


texts: Stored procedure execution duration and sending email to DBA if it run long

texts:
Does anyone has a T-SQL query which checks the stored procedure run duration and inform DBA team if it run long may be more than 1 min.

Any help would be appreciated

Thanks


texts: How do I facilitate access to an Azure based SQL View for non-SQL users on Prem?

texts:
Afternoon

I'm hoping this one is simple and my general lack of Azure knowledge is the problem, but I need to present data from within a view to some non-SQL end users via some form of application / web view.

Is there a simple tool or function anyone is aware of in Azure that will allow this?  Something akin to a traditional SSRS front end where they can navigate to and run this simple report for example?

Thanks in advance for any direction you can provide.


texts: backup the difference with sql server express between 2 timestamps

texts:
I am newbie to sql and have sql server express edition installed on my PC and contains
a database 'Jdatabase' on 1sept and took a backup(by right clicking on database -> tasks->backup using 'sql server management studio') of it and backup is 20GB
after 1 month , I took the backup  for the same database and it increased to 35GB.
Likewise saving the backup is going to be huge after certain time and is a problem for me.

If I want to backup only the difference ie., 15GB that has grown in 1 month time.
what is the best way to do it using SQL server express.


texts: Maximize redo rate on replica side of an Availability Group

texts:
My SQL Server databases are hosted on a highly spec'd converged platform with All Flash Storage Arrays, 40G network etc.

However, we're still unable to get more than 50MB/s Redo Rate - I need this to be in the GB/sec range.

The supporting platform can handle this - what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it? Even with a single-threaded process 50MB/s is poor - and totally useless given the amount of data we process.

Updates:
When I'm on the server I can copy files between drives and get speeds > 1000MB/s.
After reboots I see Redo Rates spike as high as 300MB/s, so I know its possible in my environment, however it quickly reduces to its norm of 50MB/s even when the Recovery queue is sitting at 400G. That takes 8 hours to drain - leading to very unhappy Clients...


texts: MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

texts:
Need MySQL query Help.

Please find schema below:

Table1
PK_id    Table1_Title    Table1_Description
1        Test1           This is Test1
2        Test2           This is Test2

Table2
FK_id    Table2_Title    Table2_Description
1        Test2           This is Test2
1        Test3           This is Test3

Here "Table2" is in child relationship with Master table "Table1". I want to search in Title and Description columns of above 2 tables. If search string is found in master table "Table1" then that row along with all the rows of child should be returned. Simillarly if search string is found in one of the child tabel row then the coresponding Master table row and all its child should be returned. To clarify please find result below:

Result:

Case 1:
If searching TEST2 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3
2        Test2           This is Test2         NULL     NULL            NULL

Case 2:
If searching TEST1 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Case 3:
If searching TEST3 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Is it possible to have a result with single query or something else?

Please help.


texts: Combining 2 MySQL queries with IN clause does not work

texts:
I have a 2-step query that I am trying to combine into one.

This returns the ids:

select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
;

-- Then I take the results for parent id=26 (from prior query) and put them in an IN clause.

SELECT * FROM reactions
WHERE deleted_at is NULL AND is_removed=0 AND reactable_type LIKE "App%Comment"
AND
reactable_id IN
(
30,
31,
33,
34,
50,
51,
52,
53,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
5819,
6083,
5921,
6390,
54,
56,
57,
58,
59,
60,
61,
62,
5779
)
;

However when I combine the above 2 into one query, this does NOT work and returns a much shorter set of results:

----------------------
SELECT * FROM reactions r
WHERE r.deleted_at is NULL AND r.is_removed=0 AND r.reactable_type LIKE "App%Comment"
AND
r.reactable_id IN
(
select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
)
;

What am I doing wrong?


texts: Outer Join on all columns if possible, or a subset of columns if not possible, and sum

texts:
I have two tables, call them T1 and T2 each with 4 columns A, B, C, Quantity.
I want to do an outer join. However, some ABC combinations in T1 do not match with T2, and vice versa.
My goal is to join with the following logic:
I want to join on all columns ABC if possible and return T1.A,T1.B,T1.C,T2.Quantity.
If there is no match at A,B,C then for a given T1.A, T1.B, join with T2.A, T2.B, sum(T2.Quantity). That is, I group by all T2.A, T2.B that match the T1.A, T1.B and get the sum of all T2.Quantity in that group.
I know this can be done by grouping T2 in subqueries and then left joining on T1, but could this be done with window functions?
Example:
Let T1:
A = 1
B = 2
C = 3
Quantity = 10
T2 Row1:
A = 1
B = 2
C = 4
Quantity = 20
T2 Row2:
A = 1
B = 2
C = 5
Quantity = 30
I want to return:
T1.A = 1
T1.B = 2
T1.C = 3
SUM(T2.Quantity) = 50 (sum of quantity in both rows in T2 that match T1.A,T1.B since T1.C do not match)


texts: Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.


texts: Different way to do a LIKE WHERE condition?

texts:
I have a query which works fine:

  SELECT
         month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType
          ,COUNT(MessageType) AS MESSAGE_COUNT
  FROM Message
  WHERE year(SendTime)= 2014
      and messagetype NOT LIKE '%test%'
      and messagetype NOT LIKE '%ANOTHER THING%'
      and messagetype NOT LIKE '%STUFF%'
  GROUP BY month(SendTime),year(sendtime), MessageType

What I notice is with having LIKE conditions it has made the
the elapse time slower.

With the above query it runs at elapsed time .550, without them .154.

Is there a different way I can be able to cut this time and make query faster?

No messagetype is not index and i am not allow to change it.


texts: Best practices for generating a tnsnames.ora entry

texts:
I need to integrate some scripts that export and import data from Oracle databases. They are shell scripts that rely on the TNS name being set up (e.g., scott/tiger@tns).

The application owners are likely not going to change the scripts to support EZConnect syntax, so my best option right now is to automate the TNS name setup. The file, for reference, sits here:

$ORACLE_HOME/network/admin/tnsnames.ora

Is there already a best practice / existing utility for doing this? Or am I just going to have to write a shell script to pump out the task?


texts: my ssh is ok but masterha_check_ssh is fail

texts:
my environment is vmware12 + centos6.6 + mysql5.5 + mha4mysql-manager-0.53-0.noarch.rpm + mha4mysql-node-0.53-0.noarch.rpm

my structure is one master three slaves

master  192.168.233.135 node
slave1  192.168.233.132 node
slave2  192.168.233.133 node
slave3  192.168.233.134 node + manager

My root@slave3 to root@master is OK!

[root@slave3 ~]# ssh root@master
Last login: Fri Jun  3 08:03:21 2016 from 192.168.233.134

[root@slave3 ~]# ssh root@192.168.233.135
Last login: Fri Jun  3 08:38:00 2016 from 192.168.233.134

but when I use masterha_check_ssh it fails

[root@slave3 ~]# /usr/bin/masterha_check_ssh --conf=/etc/app1.cnf
Tue May 31 17:08:23 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue May 31 17:08:23 2016 - [info] Reading application default configurations from /etc/app1.cnf..
Tue May 31 17:08:23 2016 - [info] Reading server configurations from /etc/app1.cnf..
Tue May 31 17:08:23 2016 - [info] Starting SSH connection tests..
Tue May 31 17:08:26 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Tue May 31 17:08:25 2016 - [debug]  Connecting via SSH from root@slave3(192.168.233.134:22) to root@master(192.168.233.135:22)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Tue May 31 17:08:26 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln107] SSH connection from root@slave3(192.168.233.134:22) to root@master(192.168.233.135:22) failed!
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:24 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@master(192.168.233.135:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@slave2(192.168.233.133:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave1(192.168.233.132:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:23 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave1(192.168.233.132:22)..
Tue May 31 17:08:25 2016 - [debug]   ok.
Tue May 31 17:08:25 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave2(192.168.233.133:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@master(192.168.233.135:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
Tue May 31 17:08:28 2016 - [debug]
Tue May 31 17:08:24 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@master(192.168.233.135:22)..
Tue May 31 17:08:26 2016 - [debug]   ok.
Tue May 31 17:08:26 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@slave1(192.168.233.132:22)..
Tue May 31 17:08:27 2016 - [debug]   ok.
Tue May 31 17:08:27 2016 - [debug]  Connecting via SSH from root@slave2(192.168.233.133:22) to root@slave3(192.168.233.134:22)..
Tue May 31 17:08:28 2016 - [debug]   ok.
SSH Configuration Check Failed!
 at /usr/bin/masterha_check_ssh line 44

and my /etc/app1.cnf is

[root@slave3 ~]# cat /etc/app1.cnf

  [server default]
  user=root
  password=123456
  ssh_user=root
  master_binlog_dir= /data/mysql3306/logs/
  remote_workdir=/tmp
  secondary_check_script= /usr/bin/masterha_secondary_check -s slave3-s slave2
  ping_interval=3
  master_ip_failover_script=/script/masterha/master_ip_failover
  shutdown_script= /script/masterha/power_manager
  report_script= /script/masterha/send_master_failover_mail
  manager_workdir=/var/log/masterha/app1
  manager_log=/var/log/masterha/app1/app1.log

  [server1]
  hostname=master

  [server2]
  hostname=slave1
  candidate_master=1

  [server3]
  hostname=slave2
  candidate_master=1

  [server4]
  hostname=slave3
  no_master=1


texts: Postgresql and jsonb: perform a like over all values of an array

texts:
Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email:

["email1@foo.com", "email2@foo.com", "email3@foo.com"]

I want to be able to get a record that has an email like "email2".

I tried the following

SELECT * FROM users WHERE jsonb_array_elements(emails) like 'email2';

But it does not work (obviously).

If somebody can help... Thanks!


texts: Sync & Async Synchronisation on SQL Server 2012

texts:
Is it possible to set up async synchronisation (high-performance) on MS SQL Server 2012 globally, but somehow force synchronous replication for one table between nodes?

Nodes are in different subnets/continents.

Thank you in advance.


texts: LSN mentioned in Backup is missing in Backupset

texts:
I was testing some restore scenario for one of my requirement.
SO while i was restoring i got the below error.
--Msg 4305, Level 16, State 1, Line 79
--The log in this backup set begins at LSN 38000000116400001, which is too recent to apply to the database.
--An earlier log backup that includes LSN 38000000116100001 can be restored.

So i checked the backupset table to find the missing log backup.
But i couldn't find that LSN in the results.

I was testing a scenario that is why the full backups are taken every 15 minutes and tlog backup schedules every 5 minutes.
I notice that Full backup and Tlog backup occurs at the same time every 15 minutes.Does that create any issue while restoring.?

Also the restore scenario i am trying is as below:
I start with a full backup  restore,then i restore all tlog backups and i skip full backups which occurred in between.?
Please suggest if there is any issues in that?


texts: sync_binlog different for master and slave

texts:
Is it fine if sync_binlog is different for master and slave?

For my system I can see master has sync_binlog=1 and slave has sync_binlog=0.


texts: How do I dump a Postgres table and restore under another user/role?

texts:
I am getting an error when I try to restore a Postgres database table to a different user/role than from which it was dumped.

I am doing the pg_dump as OS user: 'postgres', and doing the restore as OS user 'postgres'.

The databases however were created as other users, not by the postgres user.

Error when running pg_restore script:

CREATE TABLE
psql:./person.sql:49: ERROR:  must be member of role "dev"
psql:./person.sql:57: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:./person.sql:58: error: invalid command \N

I do not get this error if I dump/restore to any database created by the same user/role ( 'dev' ).

I only see it when I dump a table created by one user, and then try to restore under another database created by a different user/role.

Here is my dump script ( $./dump-table )

#!/bin/bash

PGUSER=dev
PGPASSWORD=<password>
PG_DATABASE=db_dev
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql

rm -f ./*.sql
pg_dump \
   --format plain \
   --verbose \
   --file $SQL_FILE \
   --table $TABLE $PG_DATABASE

Here is my restore script ( $./restore-table )

#!/bin/bash

PGUSER=prod
PGPASSWORD=<password>
PGDATABASE=db_prod
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql
psql -1 -f ./$SQL_FILE

Is there another parameter I need to use when dumping, or restoring the table to avoid this error?


texts:
So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing.

Prod server and Report server.

On the prod server I configured the Distributor from the UI.
Then I setup a transnational publication from the UI.
I made sure not to check either snapshot options.
Created the publication.

Changed 'Allowed initialization from backup file' to true in the Subscription options in the UI.

Now I create a full backup of the database on the prod server and restore it to the report server. I disable all triggers and constraints on the report server.

Now also on the prod server I want to create the subscription for the report server.
I choose to run all agents at the Distributor.

I choose 'Add Subscriber' and point it to the report server.
Agent Schedule is set to Location: Distributor and Schedule: Run continuously

Subscription Properties are set as follows
Initialize: checked
Initialize When: At first synchronization

I generate the script file and edit it as below

use [Demo]
exec sp_addsubscription
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@destination_db = N'Demo',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@backupdevicetype ='disk',
@backupdevicename = '\\backup device path.bak',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

And next I left unmodified

exec sp_addpushsubscription_agent
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@subscriber_db = N'Demo',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20140206,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'

Log Reader Agent Status once in awhile says 'x transaction(s) with x command(s) were delivered'

Now looking at the logs this morning I see the following error

'Cannot insert an explicit value into a timestamp columns. Use INSERT with a column list to exclude the timestamp column, or a default into the timestamp column'

Now I have read some posts saying this is because the row timestamp column.
I have 1,000's of tables. If this is the issue what is the fastest way to change correct it?

Also after the Synchronization status errors and starts back up, Why does it always say Initializing? This alone takes over an hour before it errors out again.

Update
I found in article properties that I can change timestamp to binary(8).
Do I need to take another backup and restore it after this change?
How will this effect the production server application along with the reporting application?


texts: Is it possible to change the Access data source to SQL Server?

texts:
I'm currently managing a "db" in Access (meh). A complex structure of masks and VBA scripts was built over this "db".

What I'd like to do is change the Access datasource from Access itself to SQL Server. I've already upsized and imported the whole data. In this way the users keep managing the system using the old Access masks & scripts but the data is inside SQL Server.

Is this possible? Or do I have to create the whole managing structure from scratch?

My problem is that more than one "client" have to manage the data simultaneously, that's why I'm searching for the magic "edit data source" button. The upsize seems to link a single instance of Access to the new data source.


texts: How to set rank per group with a SQL UPDATE statement?

texts:
Please first refer to the table structure:

Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year.

I need to rank the managers for every group which members having the same branch_code, year and day of year. Here branch_code stands for the department code.

I tried this:

I could get the correct rank number using RANK() on SQL Server but I don't know how to set the correct_rank back into the rank_in_department column using an UPDATE statement on table open_account_by_manager_per_day.

Do you have any idea on how this can be done?


texts:
I have been trying to run a MySQL database in a Docker container using the official image. I can run everything OK but I have a problem with inserting accents.

The thing is that I can not insert accents on the MySQL REPL (or CLI or shell or... I am not sure how to call it; I mean where you see the mysql> prompt). Nonetheless I can exit to the main Linux shell (prompt #) and insert accents normally.

I know that UTF8 is not reliable in MySQL so I tried to set utf8mb4. The character settings I do is:

mysql> set names utf8mb4;
mysql> set character_set_database=utf8mb4;
mysql> set character_set_server=utf8mb4;
mysql> set character_set_system=utf8mb4;

You can see what I have:

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

Thanks.


texts: When updated records get written to the real physical pages in mdf file?

texts:
I'm a beginner in SQL Server, just some questions on transaction. My textbook says:

A checkpoint is a periodic operation
that forces all dirty pages for the database currently in use to be written to disk

I'm confused, does a checkpoint make all dirty pages written to log file .ldf?

Let's say we write some code:

BEGIN TRAN T1;
UPDATE table1
SET postcode = '9000'
WHERE CustomerID = 1;

...
...
COMMIT TRAN T1;

so by the time the 'commit' is executed, does the updated record in table1 still in cache and waiting for a checkpoint to make the updated record written in the log file?
After the updated record is written to the disk (log file), then how the updated record get written to the real physical pages in mdf file?


texts:
On a 64Bit Debian 6 OS with 4 CPU cores and 8GB RAM, I can reproduce an issue with mongostat.

Whenever I create an index, reIndex or even index in background, according to top, the CPU load is 40-60% and RAM is filling up to 90%.
While index task is running, I try mongostat and watch the CPU load. Immediately, CPU load is nearly 100% and blocking.

  mongostat

  connected to: 127.0.0.1

Nothing after that, only CPU load at the limit until Ctrl + C for stopping mongostat. Few seconds later, CPU load scales down to ~50% and all is fine again.

Trying mongostat again with the same behaviour...

Is mongodb expecting more CPUs or what's wrong?


texts:
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming.  The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a general pruning of old data on a database wide level, covering 90% of the tables -- does this constitute an appropriate use case for shrinking the database?

If not, what are the appropriate steps to take to clean house after removing such a high percentage of data from a database?  I can think of two: Rebuild Indexes and Update Stats. What else?


texts: What is the search_path for a given database and user?

texts:
I can see the current search_path with:

show search_path ;

And I can set the search_path for the current session with:

set search_path = "$user", public, postgis;

As well, I can permanently set the search_path for a given database with:

alter database mydb set search_path = "$user", public, postgis ;

And I can permanently set the search_path for a given role (user) with:

alter role johnny set search_path = "$user", public, postgis ;

But I would like to know how to determine what the database and role settings are (with respect to search_path) prior to altering them?


texts: Difference between master site and materialized view site in oracle

texts: I'm learning the replication concept in oracle database. I have a confusion of these two terms - master site and materialized view site. Please help on this...


texts:
I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter.

I'm using code to fragment a tweet into words, usernames, and hashtags, so

I'm meeting @President over coffee to talk about my new job #cabinet #woot

would break down into {meeting, over, coffee, talk, about, new, job} (getting rid of words under 2 letters and after stripping punctuation), {president}, and {cabinet,woot}.

With my lack of experience in this area, I'd like to just be able to grow the number of rows in a column as necessary, but my intuition tells me that this would be a nightmare.  Another possibility I've considered is just picking an arbitrary number like 10 columns to store words, 5 for usernames, etc., but this would be roughing it a bit, as I don't know for sure how many there will be, and I'd have to throw out what may be valuable information.

As it stands now, I have 3 varchars of sufficient length which hold "meeting over coffee talk about new job", "president", and "cabinet woot".  So, whenever I need to grab the information for a particular tweet, I retrieve the row and break up the strings.

Is this the "correct" route?  Am I missing an obvious way that will make my life easier? (I had found something online about using array, but I'm using MySQL and it doesn't seem to support that datatype.


texts:
I created a cube in SSAS, how I can extract real time data from the data source to my cube?

I mean if any data modification (insert, update, delete) happens, SSAS should update the cube.


texts: Bulk Insert into view and find missing values

texts:
I have 2 tables, Categories, and SubCategories:

CREATE TABLE [dbo].[tbCategories]
(
  [CategoryID] [int] IDENTITY(1,1) NOT NULL,
  [CategoryName] [varchar](250) NULL,
)

CREATE TABLE [dbo].[tbSubCategories]
(
  [SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
  [CategoryID] [int] NULL,
  [SubCategory] [varchar](250) NULL
)

I want to Import Sub-Categories with a csv file, which contains the CategoryName and the SubCategory.

Here's an example:

So I get the CategoryName and the Sub-Category Name.  But I need the CategoryID. The user won't know what that is, they will only know the Category Name. What would be this best way to do this?

I already have a bulk insert method (using c#) that works fine if I import a csv file with CategoryID and SubCategoryName.  I thought I can insert into a view to achieve this. Something like:

CREATE VIEW [dbo].[vSubCategories]
AS

SELECT c.CategoryName, s.SubCategory
FROM dbo.tbCategories c
INNER JOIN dbo.tbSubCategories s
ON c.CategoryID = s.CategoryID

Then I can use a trigger with 'INSTEAD OF INSERT'.  But how do I get SQL to automatically pick up the CategoryID which should be inserted into SubCategory table?

In short, I want to import the csv file above, and see something similar in the tbSubCategories table as below:


texts:
I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified):

CREATE TABLE chemicals (
    name text PRIMARY KEY,
    chem_id SERIAL UNIQUE NOT NULL
);
CREATE TABLE mixtures (
    mixture_id SERIAL PRIMARY KEY,
);
CREATE TABLE mixture_chems (
    mixture_id INTEGER REFERENCES mixtures (mixture_id),
    chem_id INTEGER REFERENCES chemicals (chem_id)
);

But I also would like to enforce that there is only one (unique) mixture_id that is referred to by any particular combination of chem_id (via the rows in the mixture_chems table).

How could I implement this in PostgreSQL?

One person suggested I might need to use triggers to compute some new value, that uniquely identifies a mixture, and then enforce uniqueness on that. Thoughts on how to implement that, or whether it'd be appropriate here?


texts:
I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer?

As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www.qdpma.com/tpch/TPCH100_Query_plans.html and here https://github.com/hortonworks/hive-testbench/tree/hive14/sample-queries-tpch. I was wondering if it's the same case for TPC-C benchmark. Does it also have a fixed number of distinct queries and where could I find them? I just have a specific task in which I need to measure the performance of each query and I thought both TPC-H and TPC-C queries are good references. Thanks!


texts: Trying to connect a PostgreSQL database to Eclipse

texts:
I have a Postgres (version 9.2) database running on a Centos 7 machine, and I'm trying to connect to it from Eclipse Oxygen on a Windows 10 machine using the Data Source Explorer.

When I put in all the information on the Connection Details screen in Eclipse and hit "Test Connection", I get this error:

org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:250)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
    at org.postgresql.Driver.makeConnection(Driver.java:454)
    at org.postgresql.Driver.connect(Driver.java:256)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.createConnection(PostgreSQLJDBCConnection.java:87)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.<init>(PostgreSQLJDBCConnection.java:47)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLConnectionFactory.createConnection(PostgreSQLConnectionFactory.java:51)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)
Caused by: java.net.SocketTimeoutException: connect timed out
    at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at org.postgresql.core.PGStream.<init>(PGStream.java:69)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:152)
... 14 more

I'm pretty new to Centos and Postgres, so I'm not entirely sure what all of this means. I know that the information that I put in to connect to it is correct. I'm able to ping the Centos machine from Windows and I'm able to ping the Windows machine from Centos. I am also able to connect to the Centos machine from Windows with Putty, if that helps.

Thanks in advance.


texts:
I have been reading around that I cannot have nested data structures in redis, and the only way to include it is to create a reference to it.
If I understand correct, a way to create a generic struct in redis would be:
hmset ARandomStringAsAKey name kostas address milky_way
I tried to store the hash in that way:
hmset ReferenceTest name kostas ref ARandomStringAsAKey
and then tried to get it back via:
hget ReferenceTest ref
but the only thing I got was a string saying ARandomKeyAsAString .
How could I possibly do that?
Thanks in advance!


texts: Prevent MySql from mangling queries by casting string to int

texts:
On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change - that's why I'm not using it as the primary key.

In my program's code, I have a load function. First thing it does is check the richter_code field for the search parameter. If it doesn't return anything, it then goes on to search the on the ID field for the same parameter.

The problem is that it seems to truncate the value as soon as it encounters an alpha character. So I'm getting complete garbage from my query. See screenshot for example.
Can I prevent mysql from changing the query?


texts: Is there a simple way in VS SSIS to swap existing DB connection manager with existing project connection manager?

texts:
I am using Visual Studio 2017 (SSDT).

I have several packages (about 50) I need to migrate to use the project deployment model (instead of package deployment).

I created a new Project and added my first SSIS package. I converted the existing database connection manager to Project connection and everything worked fine.

I then added my next package to the project. I can see 2 connection managers in the package. 1 being the existing Package connection and the other being the Project connection manager I just created.

Since both connection managers point to the same database, is there a way I can just swap the existing package connection manager with my new Project connection manager so that I won't have to go into all the different tasks in the SSIS package and update them to use the Project Connection manager as this would be very time consuming and prone to error?

I did some research and the best I could find was an article on updating the SSIS packages in a text editor by doing a find and replace. I'm hoping there is an easier more automated way of making the change within Visual Studio? Anyone have any suggestions?

In case it helps anyone:

Here's an article I found describing how to get around the issue by editing the SSIS XML

I would have thought this could be done natively within VS without having to edit the package XML.


texts: Why does the service broker require a master key after restoring to a different server?

texts:
I've been moving some databases which use the service broker to new servers recently via backup and restore.  I've noticed that once I do this, these databases start throwing the database master key required messages in the log.  Encryption isn't being used in with the broker, and no keys are set in the original server and database, so why does it require one once I restore it? Is it maybe using the service master key if a database master key isn't set? My searching hasn't turned up specifics of what happens if you don't set one originally, it just seems to be assumed you have one set.  It's been mentioned that there are issues if it is not set, but we haven't had any.

Also, does it actually require a key? I'm starting to wonder if this is just an incorrect message, and if I just do the UID update with the alter statement, if that will resolve it, or do I have to set the key when moving to a new server?

Thank you!


texts: I have two databases. One is in Oracle and the other one is in AWS RDS MySQL. How can I access my Oracle Database from AWS RDS MySQL Database?


texts: 'USE db_name' takes too long to change when login remotely

texts:
I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely

$ mysql -h ip-of-remote -u username -p

with that user credentials and try to change the database it takes to long to get Database changed while it connects (goes mysql prompt) very fast

mysql> USE db_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

On the contrary, when I ssh the mysql server and then try to login mysql prompt locally (without host) with the same user credentials and then try to change database it quickly changes and I don't get that message

mysql> USE db_name;
Database changed
mysql>

I tried to  --disable-auto-rehash in my.cnf but no luck. Please help

[mysql]
no-auto-rehash


texts: MySQL - INSERT INTO (multiple values) ON DUPLICATE KEY UPDATE ..., with BEFORE DELETE trigger

texts:
So I'm trying to do the following: one table, queries like:

INSERT INTO ... (multiple), (values) ON DUPLICATE KEY UPDATE ...

so far this works just fine, no problem. Now I would like to add a trigger which does the following: if NEW.field = 0 it deletes a row from the table (with same primary key) AND prevents the INSERT/UPDATE operation. And this is where I'm lost. How to prevent inserting/updating only that SINGLE row? My understanding is that if I do SIGNAL SQLSTATE ... that would break the whole statement, so none of the records would be inserted/updated, which is not what I want.

I was thinking doing:

INSERT INTO IGNORE... (multiple), (values) ON DUPLICATE KEY UPDATE ... (notice: IGNORE)

but... would this work fine? Does IGNORE ignores the errors for both insert AND the update trigger as well?

I know the obvious (and easy) workaround would be to just use AFTER triggers instead of BEFORE, but since it's a busy table with many thousands of insertion/update per second I wouldn't really want to do that.


texts:
If I have a table like so:

table T ( account_id bigint, event_date datetime ... );

and later make queries like so:

select ...
from T
where account_id = 'ABC'
  and event_date >= '2016-01-01'

Does the order of columns in the index matter, ie:

key (`account_id`, `event_date`)

Or

key (`event_date`, `account_id`)

Thanks!


texts:
I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much.

Here's the query. It sometimes takes up to 20-30 seconds to execute:

SELECT "schedules".*
FROM "schedules"
WHERE "schedules"."client_data_provider_id" = '3001-753'
AND "schedules"."practice_id" = 753
AND (date_scheduled >= '2016-01-12')

Here's the table, with indices:

                                          Table "public.schedules"
        Column              |            Type             |                       Modifiers
----------------------------+-----------------------------+--------------------------------------------------------
 id                         | integer                     | not null default nextval('schedules_id_seq'::regclass)
 practice_id                | integer                     | not null
 data_provider_id           | character varying(255)      | not null
 source                     | character varying(255)      |
 type                       | character varying(255)      |
 client_data_provider_id    | character varying(255)      | not null
 client_pms_id              | character varying(255)      | not null
 patient_data_provider_id   | character varying(255)      | not null
 patient_pms_id             | character varying(255)      | not null
 date_scheduled             | date                        | not null
 duration                   | integer                     |
 status                     | character varying(255)      |
 reason                     | text                        |
 notes                      | text                        |
 resource_id                | character varying(255)      |
 resource_name              | character varying(255)      |
 site_id                    | integer                     |
 api_create_date            | timestamp without time zone |
 api_last_change_date       | timestamp without time zone |
 api_removed_date           | timestamp without time zone |
Indexes:
    "schedules_pkey" PRIMARY KEY, btree (id)
    "index_schedules_change_date_for_query" btree (practice_id, api_last_change_date DESC) WHERE api_last_change_date IS NOT NULL
    "index_schedules_create_date_for_query" btree (practice_id, api_create_date DESC) WHERE api_create_date IS NOT NULL
    "index_schedules_on_api_last_change_date" btree (api_last_change_date)
    "index_schedules_on_client_data_provider_id_and_date_for_query" btree (practice_id, date_scheduled, client_data_provider_id)
    "index_schedules_on_practice_id" btree (practice_id)
    "index_schedules_on_practice_id_and_date_scheduled" btree (practice_id, date_scheduled)
    "index_schedules_on_data_provider_id" btree (data_provider_id)

Here's the results of explain on the query:

=> EXPLAIN for: SELECT "schedules".* FROM "schedules"  WHERE "schedules"."client_data_provider_id" = '3001-753' AND "schedules"."practice_id" = 753 AND (date_scheduled >= '2016-01-12')
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_schedules_on_client_data_provider_id_and_date_for_query on schedules  (cost=0.08..14.49 rows=1 width=706)
   Index Cond: ((practice_id = 753) AND (date_scheduled >= '2016-01-12'::date) AND ((client_data_provider_id)::text = '3001-753'::text))
(2 rows)

The schedules table has almost 4.5 million records. Only about 120,000 records have practice_id = 466, and 55 records with client_data_provider_id = '3001-753'

As you can see, there are multiple indices on the table, including one that I thought would work specifically for this query (index_schedules_on_client_data_provider_id_and_date_for_query).

We're aiming for <1 sec response. 20-30 is way too long. How would I go about improving the performance of this query?


texts: MySQL import LOCK doesn't prevent PRIMARY key issues

texts:
I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks).

Some info;

my tables are almost all intertwined with each other by Foreign Key bindings.
I'm currently having over 100 tables that hold all my data.

What I'm trying to achieve is some mechanism with mysqldump to dump my data, like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql

..and restore it like so;

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysql -u root -p my_database < my_database_data.sql

..but then with the ability to store the following on the dump (and thus restore it that way on import);

When doing mysqldump

Store the PRIMARY key counters

When importing my_database_data.sql

LOCK the tables fully (even ignoring PRIMARY key counter checks)
INSERT all data (incl. primary keys)
Set the PRIMARY key counter back to what was saved
UNLOCK the tables again

Other issue

My first thought was to just ignore the PRIMARY keys somehow and just look-increment them again on insertion. The problem is that I can't do this due to their bindings with Foreign Keys, that would then thus break.


texts: Migrate unknown Oracle database user's encrypted password - 11g hash to 12c database

texts:
I have tried to search extensively but could not find.Oracle has changed the encryption (spare4) in 12c.

I am trying to see if its possible to migrate / copy the encrypted password hash of a user in 11g database to 12c.

I have done this before on 11g to 11g, but not able to replicate / find how to do this on 11g to 12c.

Thanks!