Tag: database

data structures – Resources about implementing a database table

I’d like to understand more how data can be stored on disk so it can be read quickly. I don’t want to say database record because I’m not asking how to build a database. My question is closer to how would I build a table.

I’m not asking about indexing. I’m thinking about you have an application that already has some a pointer to a record.

I’ve been trying to find some references e.g. papers, books, but everything so far focuses on database design as in writing DDL or structures for indexing like B+ trees.

Are there any references that talk about how tables are actually implemented and what are the tradeoffs?

email – Why does this guide encrypt its passwords in its own database?

I’m referring to this guide, notably this part here:

INSERT INTO `servermail`.`virtual_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('firstpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
('2', '1', ENCRYPT('secondpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');

Firstly, ENCRYPT doesn’t work in the current iteration of MySQL, and recommends SHA2() instead for one-way hashing. I know that hashing passwords is common for security reasons — is that what’s going on here? So, if the user inputs their unhashed password, that’ll be automatically converted to its hash and compared here? Is the unencrypted password stored anywhere?

MySQL upgrade via mysqldump – Database Administrators Stack Exchange

We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) and import our exported database tables into a fresh new MySQL 8.0 installation

Should it be successful or should we do something else in the middle for it to work?

database – How to migrate PDF files to specific node

I have a directory with PDF files and I have 45 nodes of content type Download. I have to migrate each PDF file to specific node. All PDF file names I read from a CSV. Before these PDF files, I had to migrate title (drupal default field) , external title and a checkbox field to each node. I did it successfully, but migrating PDF files is I guess different and a lot more difficult.

Here is my code for migrating PDF files

id: program_files
label: Migrating files for Download content type.
migration_tags:
  - file
source:
  plugin: csv
  path: 'modules/custom/csv_migration/sources/downloads.csv'
  header_row_count: 1
  ids: (constants/file_source_uri)
  keys:
    - PLACEMENTPATH
  fields:
    0:
      name: field_document_file
      label: 'Download file'
  constants:
    file_source_uri: (public://import/program)
    file_dest_uri: ('public://download/files')
destination:
  plugin: 'entity:file'
process:
  file_source:
    -
      plugin: concat
      delimiter: /
      source: (constants/file_source_uri, PLACEMENTPATH)
  file_dest:
    -
      plugin: concat
      delimiter: /
      source: (constants/file_dest_uri, PLACEMENTPATH)
  filename: PLACEMENTPATH
  uri:
    -
      plugin: file_copy
      source:
        - '@file_source'
        - '@file_dest'

Once I run command drush ms it displays me migration id (program_files), but after that when I run drush mim program_files, it throws the error like this.

In Connection.php line 744:
                                                                                            
  Placeholders must have a trailing () if they are to be expanded with an array of values.

The thing I notice once I start drush mim program_files is that directory download is created in sites/default/files, since I defined that in YML, but no files are transfered.

What am I doing wrong? Please, help.

database – Moving from mysql to MongoDB

I am have spent years working in the LAMP stack, I am now moving to a MEVN stack and as a result need to get my around mongodb, particular how I would articulate 1:n and n:n relationships that I would traditionally user in MySQL.

A many to many would ordinarily have a pivot table in MySQL but my understanding is that mongodb, it’s basically just one big document/object. So for example if I have a products table and a suppliers table in mysql and many suppliers can have many products, I would create a table like this,

CREATE TABLE products_suppliers (
     productID   INT UNSIGNED  NOT NULL,
     supplierID  INT UNSIGNED  NOT NULL,
                 -- Same data types as the parent tables
     PRIMARY KEY (productID, supplierID),
                 -- uniqueness
     FOREIGN KEY (productID)  REFERENCES products  (productID),
     FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
   );

How would I represent this in mongodb? Would I just have a supplier of each product, or on each supplier a products array?

I am very confused, but I need to move to mongodb for future career development.

database design – if Transaction 2 then attempted to UPDATE that row as well, a serialization failure would occur

Transaction 1
/* Query 1 */

 SELECT * FROM users WHERE id = 1;

Transaction 2

/* Query 2 */

UPDATE users SET age = 21 WHERE id = 1;
COMMIT; /* in multiversion concurrency
          control, or lock-based READ COMMITTED */

/* Query 1 */

SELECT * FROM users WHERE id = 1;
COMMIT; /* lock-based REPEATABLE READ */

Under multiversion concurrency control, at the SERIALIZABLE isolation level, both SELECT queries see a snapshot of the database taken at the start of Transaction 1. Therefore, they return the same data. However, if Transaction 2 then attempted to UPDATE that row as well, a serialization failure would occur and Transaction 1 would be forced to roll back.

If Transaction 2 attempted to UPDATE that row, how would a serialization failure occur?

mysql – Keep getting this error for wordpress database set-up on my pi? ERROR 1044 (42000): Access denied for user ‘pi’@’localhost’ to database ‘wordpress’

As I’m setting up my wordpress database I keep getting this error:

ERROR 1044 (42000): Access denied for user ‘pi’@’localhost’ to database ‘wordpress’

After typing this and entering my password:

sudo mysql -upi -p

I got this:

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 57

Server version: 10.3.27-MariaDB-0+deb10u1 Raspbian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

MariaDB ((none))>

And this was what I typed:

MariaDB ((none))> GRANT ALL PRIVILEGES ON wordpress.* TO 'pi'@'localhost' IDENTI FIED BY 'password';

I replaced the actual password with ‘password’ in this example for security reasons, but I tried all of the passwords that I had. Nothing worked. I keep getting the same error.

I also tried removing ‘pi’@’localhost’ and changing it to just ‘pi’. I also dropped and recreated the wordpress database and tried again. It’s always the same error.

Any suggestions? I have no clue where to continue from here.

Magento Migration Tool: What should the database host, name, user and password be in config.xml?

I am hoping to migrate Magento 1.9.4.0 to 2.4.1 using the Magento Migration Tool. I have already installed the Migration tool and now I’m trying to configure the confix.xml file in:

<Magento 2 root dir>/vendor/magento/data-migration-tool/etc/opensource-to-opensource/<Magento 1.x version>

I already found and inserted the crypt_key, now I suppose to edit the following:

<source>
<database host="localhost" name="Magento1-DB-name" user="DB-username" password="DB-password"/>
</source>
<destination>
<database host="localhost" name="Magento2-DB-name" user="DB-username" password="DB-password"/>
</destination>

  • Localhost –> I’ve put the IP address of both the source and destination (they are both in the same hosting package but have different IP addresses) – Is this correct?

  • name –> I copied the database name from phpMyAdmin – Is this correct?

  • user –> what should this be? Is this the cPanel user name or the Magento Admin user name?

  • password –> again – cPanel password OR Magento Admin password?

After I get the correct settings in here, can I just run these 2 commands via SSH (Putty) to make the Migration tool migrate all the important data?

php bin/magento migrate:settings --reset <path to your config.xml>

php bin/magento migrate:data --reset <path to your config.xml>

Also, do I need to put the Magento 1 store in maintenance mode prior to doing the migration?

mysql – About database design

I am considering data modeling (table design) And DB types(MySQL, MongoDB and so on).

I am thinking of a service that handles financial information and economic indicators.
Options have daily data by price range and by Special Quotation.On the other hand, economic indicators have daily or monthly data, and there is breakdown data. For example, the relationship between GDP and personal consumption, housing investment, etc.

In such a case, I would like to design a table that stores data as simply as possible, but what kind of architecture should I adopt?

azure sql database – Vertical Elastic Query is not identifying Object

I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just for illustration.

CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>'; 
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..

All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB. The underlying error message received was: ‘Invalid object name ‘SalesLT.Addresstest’.’.

I didn’t get the error msg as the method I followed is used/shown in multiple website and tutorials.